Often users will export a Data & Insights dataset and open it with Excel to make some modifications. When the dataset is uploaded again, they discover that their time formats have changed and no longer match the existing transforms.
Excel stores dates as numbers, and it uses up to 16 digits when creating the date format. The digits to the right of the decimal are interpreted as hours, minutes, seconds, and milliseconds. Therefore a default timestamp upload from Excel will often appear this way: '%Y-%m-%dT%H:%M:%S%.3f', where the %f refers to the fractional seconds (in nanoseconds) since last whole second, and %3f limits that to three digits.
The table shows the most common date formats uploaded by CSV and Excel files, and the appropriate timestamp specifiers to use to interpret them.
| Source format example | Transform | Comments |
| 4/12/20 12:00 | '%-m/%-d/%-y %-H:%M' | Employs space padding. Without padding 1/1/20 won’t import |
| 4/12/2020 12:00:00 AM | '%-m/%-d/%Y %I:%M:%S %p' | Uses 12 hour time |
| 4/12/20 | '%-m/%-d/%y' | often used by CSV when no time component given |
| 4/12/2020 12:00:00 AM |
'%Y-%m-%dT%H:%M:%S%.3f' |
A default timestamp used by Excel, as it converts numbers to a datetime datatype |
And finally, if you are unsure what the best timestamp to use for the DateTime formats your source data has, uploading your dataset to a new, test dataset is a good way to see what specifiers Data & Insights would apply to the current values in the source file.
Comments
Article is closed for comments.