[Solved] Epoch to date/time

[donnaroger211]

I’m trying to convert epoch numbers to date/time and following the instructions in this website, all I get is an infinite row of #’s.

I copied the formula, changed the cell reference to my worksheet, copied the value of the output to a new column, tried to format - output was ################ and so on.

 

[shamimarita]

Hello,

It looks like the issue is likely related to one of these common problems:

Cell Width Too Small: If the column isn't wide enough, Excel will display #####. Try expanding the column width.

Incorrect Formula or Reference: Ensure you're using the correct formula, like:
=A1 / 86400 + DATE(1970,1,1)
(Replace A1 with your actual cell reference.)

Wrong Date Format: After applying the formula, format the cell as a date/time (Ctrl + 1 → Select "Date" or "Custom" format).

Negative Values: If your epoch time is in milliseconds, divide by 1000 first. If the number is negative or too large, Excel may not recognize it.

Let me know what format your epoch time is in, and I can help troubleshoot further!

 

[donnaroger211]

Hello,

It looks like the issue is likely related to one of these common problems:

Cell Width Too Small: If the column isn't wide enough, Excel will display #####. Try expanding the column width.

Incorrect Formula or Reference: Ensure you're using the correct formula, like:
=A1 / 86400 + DATE(1970,1,1)
(Replace A1 with your actual cell reference.)

Wrong Date Format: After applying the formula, format the cell as a date/time (Ctrl + 1 → Select "Date" or "Custom" format).

Negative Values: If your epoch time is in milliseconds, divide by 1000 first. If the number is negative or too large, Excel may not recognize it.

Let me know what format your epoch time is in, and I can help troubleshoot further!

OMG! Shamimarita! You are a saint! I divided the epoch by 1,000 and suddenly my formula worked! I was actually using this formula, which I got off the exceldemy website: =(((I2/60)/60)/24)+DATE(1970,1,1) I'm good now and thank you very much! This will be extremely helpful - when I tried to explain to my husband what I was trying to do, his eyes glazed over when I mentioned 'excel' He's a Luddite but I love him anyway. Thanks again!

 

[shamimarita]

Haha! Glad you got it working! Sometimes, it's just that one little tweak that makes all the difference. And hey, explaining Excel to a Luddite is a heroic effort in itself—props to you for trying! Happy Excelling with ExcelDemy!