0

I have timestamp values, but the calculated duration difference between them does not seem right.

Start Time, End time
23/04/2021 23:45.39.824, 23/04/2021 23:45.41.807 
24/04/2021 05:38.49.610, 24/04/2021 05:39.03.250

The calculated values are (roughly)

Start Time, End time, Duration Unformatted, Duration Formatted
23/04/2021 23:45.39.824, 23/04/2021 23:45.41.807, 2.29514E-05, 00/01/1900 00:00:01.983
24/04/2021 05:38.49.610, 24/04/2021 05:39.03.250, 0.00015787, 00/01/1900 00:00:13.640

The formula i used to calculate the "Duration columns is:

=b2-c2

The "Start Time" and "End Time" have the following format:

dd/mm/yyyy hh:mm:ss.000

Question

Now why does the first item have a formatted value displaying 1 second and 807 milliseconds but an unformatted value of 2.29524E-05 and the second has a 13 second and 640 millisecond with an unformatted value of 0.00015787?

I need to know this because this is messing up my calculations after the fact!

YellowPill
  • 135
  • 1
  • 11

1 Answers1

0

Excel stores dates and times as float values in cells.
1 will translate to 1900-01-01 00:00:00.

1.5 will translate to 1900-01-01 12:00:00.

2.5 is 1900-01-02 12:00:00.

So 1 second is 1/86400 in Excel. That is why you get those small numbers in return.
You can convert the numbers too seconds by multiplying with 86400.

(I have comma as decimal point) enter image description here

Andreas
  • 23,610
  • 6
  • 30
  • 62
  • True... My bad.. I confused it with UNIX – Andreas Apr 28 '21 at 07:27
  • 1
    it even offers nice secrets ^^ [December 30, 1899 as base date](https://social.msdn.microsoft.com/Forums/office/en-US/f1eef5fe-ef5e-4ab6-9d92-0998d3fa6e14/what-is-story-behind-december-30-1899-as-base-date?forum=accessdev) – FObersteiner Apr 28 '21 at 07:28