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.


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

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:

  1. You can create 2 columns includes hours and minutes and add a drop down, then generate time from those.

  2. You can add time correction to field. : between hour and minute concert valid time field.


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.

Hour Tracker

  • Thanks for your Hour Tracker ... I just copied it :) – Rafaf Tahsin Apr 14 '19 at 4:19

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

  • This site is about web applications, not native mobile applications. – user135384 Jan 28 '18 at 2:05
  • ok. I will take that. Nevertheless, it's still a solution to the problem. – inode Jan 28 '18 at 15:11
  • I just wanted to point out...Google Docs, Sheets, Calendar, and most of the G-Suite apps are mobile web app, not native mobile applications, because the data is served from the web. They are hybridized, but still fall within the web applications realm to some degree. – MistyDawn Jul 11 '18 at 20:41
  • the picker element happens to be more critical to me on mobile than on a desktop browser, so this perfectly fits my particular use case. +1 – nar8789 Sep 12 '19 at 21:14

Perhaps the following piece of code will work for you.


function onEdit(e) {
  var str = e.range.getValue();
  if(str.substring(0, 5) == "_time") {
    var v = str.match(/\d+/g);    
    if(!v) {
    } 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");


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.


enter image description here

enter image description here


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.


https://webapps.stackexchange.com/a/44404/29140 (upvote?)

  • 1
    I don't get it. I want a visual (GUI) time picker, similar to a datetime picker. This is even harder than typing 00:00. I was able to execute your code but it requires me to manually type it. And 90% of my use cases are not the current time. – braindamage Mar 7 '15 at 16:11
  • @braindamage I guess you are right. – Jacob Jan Tuinstra Mar 7 '15 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.