Forum Discussion
Solved
convert numbers in scientific (exponential) notation into date format
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
It looks like that's UNIX timestamp in milliseconds. Formula could be
=(A1/86400/1000)+25569and apply date or datetime format.
Hi Jackie321,
you can try these steps:
Step 1: Format the cell containing the number in scientific notation
- Select the cell containing the number in scientific notation.
- Right-click and choose "Format Cells."
- 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
- Right-click on the cell containing the formula result and choose "Format Cells."
- In the "Number" tab, select the "Date" category.
- 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)I guess Step 1 is not necessary
It looks like that's UNIX timestamp in milliseconds. Formula could be
=(A1/86400/1000)+25569and apply date or datetime format.
- Thank you, Sergei. Above formula worked.
Jackie321 , you are welcome
