Issues Entering Datetime Values

I’m having problems entering datetime values.

If I enter “5/5/2020 4:50 AM” into a blank worksheet, it displays what I enter, but it’s not a date. Currently, it’s fairly difficult to verify if something is a date without performing some math on it, such as =A1+1 to see if it adds one day.

If I have “5/5/2020 4:50 PM” in a cell and then change the data type to a Date Time value, it doesn’t convert what is currently in the cell correctly (or it deletes the value entirely). This could be a significant issue if you’ve imported a bunch of data and want to convert an entire column to dates.

When I am using a Date Time data type, I can set it to strict, but if I press F2 to enter a date, it pops up the date picker (which is fine) but it doesn’t let me start typing. I have to click into the cell to begin typing (and there is some extra white space in there). And, if I press Esc or decide to not enter a date and mouse away, it automatically enters 12/30/99 12:00 AM.

I would like the Date Time data type to include some time-only formats, such as “h:mm AM/PM”, “[h]:mm”, “hh:mm:ss”, “hh:mm”, etc. You don’t need a separate Time data type, but including these time-only formats would be very helpful.

I’m assuming that the way SSDC stores dates and times is the same as Excel and Google Sheets, where the integer portion of the value represents days and the decimal portion of the value is the time.

Our Date parsing module is not robust yet. We are aware of this limitation. We will be working on this in the coming weeks/months.
We aim to make it as robust and diverse as GSheet’s. So that should take care of “5/5/2020 4:50 AM” etc.

“I’m assuming that the way SSDC stores dates and times is the same as Excel and Google Sheets, where the integer portion of the value represents days and the decimal portion of the value is the time.”
-> Correct. a day = 1 , starting on 1900 Jan1

Another related issue is that if you double-click on a date cell and then choose not to enter a date (by clicking away from the date picker), spaces are added to the cell (so it’s not blank), which can mess up formulas that may be referencing the cell.

The following issues were fixed in recent updates:

If I have “5/5/2020 4:50 PM” in a cell and then change the data type to a Date Time value, it doesn’t convert what is currently in the cell correctly (or it deletes the value entirely). This could be a significant issue if you’ve imported a bunch of data and want to convert an entire column to dates.

When I am using a Date Time data type, I can set it to strict, but if I press F2 to enter a date, it pops up the date picker (which is fine) but it doesn’t let me start typing. I have to click into the cell to begin typing (and there is some extra white space in there). And, if I press Esc or decide to not enter a date and mouse away, it automatically enters 12/30/99 12:00 AM.

2 Likes