I don't need timezones or anything complicated, just a string on the HH:MM format, that matches the custom format available. I don't need days, months or anything like that, just the time.
Asked
Active
Viewed 1.1e+01k times
12
pnuts
- 17,883
- 5
- 55
- 103
braindamage
- 171
- 1
- 1
- 5
6 Answers
13
Go to Data > Data Validation
Select "List of Items"
Paste this in the box:
01:00A, 01:15A, 01:30A, 01:45A, 02:00A, 02:15A, 02:30A, 02:45A, 03:00A, 03:15A, 03:30A, 03:45A, 04:00A, 04:15A, 04:30A, 04:45A, 05:00A, 05:15A, 05:30A, 05:45A, 06:00A, 06:15A, 06:30A, 06:45A, 07:00A, 07:15A, 07:30A, 07:45A, 08:00A, 08:15A, 08:30A, 08:45A, 09:00A, 09:15A, 09:30A, 09:45A, 10:00A, 10:15A, 10:30A, 10:45A, 11:00A, 11:15A, 11:30A, 11:45A, 12:00P, 12:15P, 12:30P, 12:45P, 01:00P, 01:15P, 01:30P, 01:45P, 02:00P, 02:15P, 02:30P, 02:45P, 03:00P, 03:15P, 03:30P, 03:45P, 04:00P, 04:15P, 04:30P, 04:45P, 05:00P, 05:15P, 05:30P, 05:45P, 06:00P, 06:15P, 06:30P, 06:45P, 07:00P, 07:15P, 07:30P, 07:45P, 08:00P, 08:15P, 08:30P, 08:45P, 09:00P, 09:15P, 09:30P, 09:45P, 10:00P, 10:15P, 10:30P, 10:45P, 11:00P, 11:15P, 11:30P, 11:45P, 12:00A, 12:15A, 12:30A, 12:45A
4
If you want to use forms there is a time picker element to select time. Otherwise, you don’t have a time picker.
You got 2 options:
You can create 2 columns includes hours and minutes and add a drop down, then generate time from those.
You can add time correction to field.
:between hour and minute concert valid time field.
Ayhan
- 41
- 3
3
You can download Google Sheets app from Play Store on your mobile device.
- Format any column for Date/Time/DateTime format
- Go to sheets app on mobile and try entering a value to that column
Result: - You get a date time picker to choose date and time
inode
- 41
- 2
2
In a hidden sheet enter the times that you would want to be able to select from, either half hour or 15 minute intervals in one column. Make sure that it is in an appropriate time number format so that it will transfer to any other formulas you may have.
Put data validation on the column you would like to have your time picker on, where the data must be from the column in your hidden sheet.
This will produce a drop down menu from which you can select a time.
The following link is to an example of an hour tracker with the time sheet unhidden.
jomortal
- 21
- 1
1
The same as top answer, but maybe you want 24hr time format
- Select cells you want
- Go to Data -> Data validation
- Select "List of items"
- Paste the list below into the window near "List of items" option
- Press Save button
00:00, 00:15, 00:30, 00:45, 01:00, 01:15, 01:30, 01:45, 02:00, 02:15, 02:30, 02:45, 03:00, 03:15, 03:30, 03:45, 04:00, 04:15, 04:30, 04:45, 05:00, 05:15, 05:30, 05:45, 06:00, 06:15, 06:30, 06:45, 07:00, 07:15, 07:30, 07:45, 08:00, 08:15, 08:30, 08:45, 09:00, 09:15, 09:30, 09:45, 10:00, 10:15, 10:30, 10:45, 11:00, 11:15, 11:30, 11:45, 12:00, 12:15, 12:30, 12:45, 13:00, 13:15, 13:30, 13:45, 14:00, 14:15, 14:30, 14:45, 15:00, 15:15, 15:30, 15:45, 16:00, 16:15, 16:30, 16:45, 17:00, 17:15, 17:30, 17:45, 18:00, 18:15, 18:30, 18:45, 19:00, 19:15, 19:30, 19:45, 20:00, 20:15, 20:30, 20:45, 21:00, 21:15, 21:30, 21:45, 22:00, 22:15, 22:30, 22:45, 23:00, 23:15, 23:30, 23:45
Nist
- 21
- 4
0
Perhaps the following piece of code will work for you.
Code
function onEdit(e) {
var str = e.range.getValue();
if(str.substring(0, 5) == "_time") {
var v = str.match(/\d+/g);
if(!v) {
getTime(e);
} else if(!v[1]) {
getTime(e, v[0], '0');
} else {
getTime(e, v[0], v[1]);
}
}
}
function getTime(e, hrs, min) {
var d = new Date();
if(hrs) {d.setHours(hrs)};
if(min) {d.setMinutes(min)};
var timeFormat = Utilities.formatDate(new Date(d), "GMT+1", "HH:mm");
e.range.setValue(timeFormat);
}
Explained
The onEdit trigger works when a cell is being edited. When triggered it will capture the value and checks whether the first 5 characters match the string _time. This will be the secret word to initiate the trigger. A regex will extract the parameters passed on. When no extra parameters are given, like hours and minutes, then simply give the current time. When no minutes are given (_time(4)), then only show the full hour notation. When minutes are included as well, like _time(4, 6), then show the complete time.
Screenshots
before
after
Notes
Copy the code and paste it into the script editor (Tools > Script editor) and then press the save button.
Please pay attention to the input, as error handling is not present. Hours and minutes beyond our excepted range (minus or > 24) wil be re-calculated into a value.
