SOLVED

Copper Contributor

When downloading a .csv file from an online utility database, the Start and End Date columns lose their original date format (Ex: Jul 14, 2022). I've tried changing the format to "Short Date", "Long Date", and "Custom Date". No Luck. Any suggestions? See below for how the data look after I open the downloaded .csv file.

 Start Date End Date 1.65777E+12 1.66054E+12 1.65242E+12 1.65518E+12

Thank you in advance for any help!

2 Replies
best response confirmed by sbryson92 (Copper Contributor)
Solution

It looks like the dates have been specified as the number of milliseconds since January 1, 1970.

Let's say start dates are in A2 and down, and end dates in B2 and down.

Enter the following formula in C2:

=DATE(1970,1,1)+A2/86400000

Format C2 as a date or as date plus time. You'll see the first start date.

Fill to the right to D2 to see the first end date.

Then fill down.

Yep, that fixed it - thank you very much!
1 best response

Accepted Solutions
best response confirmed by sbryson92 (Copper Contributor)
Solution

It looks like the dates have been specified as the number of milliseconds since January 1, 1970.

Let's say start dates are in A2 and down, and end dates in B2 and down.

Enter the following formula in C2:

=DATE(1970,1,1)+A2/86400000

Format C2 as a date or as date plus time. You'll see the first start date.

Fill to the right to D2 to see the first end date.

Then fill down.