I want to create easy-to-use input fields for picking date and time in Google Sheets, preferably using only mouse. Date picker is straightforward enough, but choosing time is problematic. Obviously there is no native solution in Google Sheets for picking hours and minutes other than editing a formatted string, which is inconvenient and error-prone. Some people use data validation menu to select a time value from a list, which I found easy to use, so I thought I'd have one cell for picking date using date picker and another for time using a menu with data validation:
When I create a helper column to contain the time values, I start with 0:00, then 0:15 if I want 15-minute intervals, and then extend the value range to generate rest of the values automatically.
0:00
0:15
0:30
0:45
I ran into some issues however. First, the picker menu displays values in wrong format. I prefer them to be in 24-hour format without seconds.
Also there doesn't seem to exist any specific data type for "time of the day", so these values are probably just plain old dates formatted to display hours and minutes. All other digits seem to default to 0, resulting in dates starting from 30.12.1899. If I call TO_DATE() on these values, the output is:
12/30/1899 0:00:00
12/30/1899 0:15:00
12/30/1899 0:30:00
and so on. I thought this wouldn't be an issue, because the time fields are correct. But if I log these values in the script editor, I get the same value plus 4 minutes 35 seconds. I can imagine this has something to do with leap seconds or some other irregular adjustments in time coordination long time ago that I know nothing about. This compensation causes errors when I try to use the Date object in my code, because Date.getMinutes() and Date.getSeconds() don't match the displayed value. Should I decompensate these by substracting 4 minutes and 35 seconds manually?
I tried to avoid this issue by transposing the value range in the helper column to more modern era, starting from 1/1/1970 0:00 and change the format back to hh:mm. This way the displayed value matches the Date object's hours and minutes, but for some reason this also causes the time picker menu to display each item as complete date, and that's even worse than the 12-hour format I had to deal with previously. I want to only list hours and minutes.
I guess I could build the menu using strings, but that requires extra parsing in my scripts, and it would be difficult to change the interval later.
