convert numbers in scientific (exponential) notation into date format

[Jackie321]

Hi there,

i have a data set which shows all dates and time values as numbers in scientific (exponential) notation.

(1692749193000 = 169275E+12)

Which steps do I need to take to convert these into a date?

I already tried Home tab - Number group - Long date/Short date etc which returns ##########...

Thank you

[#2 · Accepted solution]

@Jackie321 

It looks like that's UNIX timestamp in milliseconds. Formula could be

=(A1/86400/1000)+25569

and apply date or datetime format.

[#3]

Hi @Jackie321,

you can try these steps:

Step 1: Format the cell containing the number in scientific notation

1. Select the cell containing the number in scientific notation.
2. Right-click and choose "Format Cells."
3. In the "Number" tab, select the "Number" category and set the number format to "Number" with a suitable number of decimal places (e.g., 0).

This will ensure that the number is displayed in a format that can be used by the formula in the next step.

Step 2. In a separate cell, enter the following formula:

=DATE(1970,1,1) + (A1 / 1000 / 86400)

Where A1 is the cell containing the number in scientific notation.

Step 3: Format the cell containing the formula result

1. Right-click on the cell containing the formula result and choose "Format Cells."
2. In the "Number" tab, select the "Date" category.
3. Choose the desired date and time format.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)

[#4]

@LeonPavesic 

I guess Step 1 is not necessary

[#5]

Thank you, Sergei. Above formula worked.

[#6]

@Jackie321 , you are welcome