12

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.

6 Answers 6

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
1
  • You're a hero for typing this out
    – Astrydax
    Aug 13, 2021 at 3:05
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:

  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.

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.

Hour Tracker

2
  • Thanks for your Hour Tracker ... I just copied it :) Apr 14, 2019 at 4:19
  • Nice concept, but I found by accident when I pick a start time that's after the end time, the calculated hours is a negative number. That is, I had a start time of "4:30 PM" and I meant to choose an end time of "8:00 PM, but I accidentally chose "8:00 AM". So the number of hours was negative. It should always be positive. It should have calculated the hours as though it began at 4:30 PM on one day and ended at 8:00 AM on the next day. Mar 19, 2021 at 1:21
2

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

4
  • This site is about web applications, not native mobile applications.
    – user135384
    Jan 28, 2018 at 2:05
  • 1
    ok. I will take that. Nevertheless, it's still a solution to the problem.
    – inode
    Jan 28, 2018 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, 2018 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, 2019 at 21:14
1

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
enter image description here

after
enter image description here

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.

Reference

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

2
  • 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. Mar 7, 2015 at 16:11
  • @braindamage I guess you are right. Mar 7, 2015 at 18:19
1

The same as top answer, but maybe you want 24hr time format

  1. Select cells you want
  2. Go to Data -> Data validation
  3. Select "List of items"
  4. Paste the list below into the window near "List of items" option
  5. 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

1
  • Welcome to Web Applications. While it's very good that you make reference to a previous answer, each answer should be self-contained, so please add a brief explanation about how to use the list provided. Sep 10, 2021 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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