SOLVED

# convert numbers in scientific (exponential) notation into date format

Copper Contributor

# 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

5 Replies
best response confirmed by Jackie321 (Copper Contributor)
Solution

# Re: convert numbers in scientific (exponential) notation into date format

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

``=(A1/86400/1000)+25569``

and apply date or datetime format.

# Re: convert numbers in scientific (exponential) notation into date format

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

# Re: convert numbers in scientific (exponential) notation into date format

I guess Step 1 is not necessary

# Re: convert numbers in scientific (exponential) notation into date format

Thank you, Sergei. Above formula worked.

# Re: convert numbers in scientific (exponential) notation into date format

@Jackie321 , you are welcome

1 best response

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

# Re: convert numbers in scientific (exponential) notation into date format

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

``=(A1/86400/1000)+25569``

and apply date or datetime format.