CODEWITHSUNDEEP
exceldatetime
Mariska
Mariska

Reputation: 1953

Constructing Datetime with milliseconds in Excel

Suppose I have the Date object and milliseconds in 2 columns. I wanted to take the difference of the 2 consecutive Datetime and output in milliseconds as follows: 

Date                    Milliseconds            Time Lapse Difference
8/12/2014 9:30:00 AM       200                          -
8/12/2014 9:30:00 AM       800                         600
8/12/2014 9:30:01 AM       100                         300
8/12/2014 9:30:02 AM       300                        1200
8/12/2014 9:30:05 AM        0                         2700

What should the formula be for Time Lapse difference? I was trying to append the milliseconds to the Date object, but seems like Excel does not support milliseconds.

Upvotes: 0

Views: 262

Answers (1)

Siphor
Siphor

Reputation: 2534

In Excel Dates are based on Days so 2012/1/2 - 2012/1/1 = 1.
To convert the difference into milleseconds we have to multiply by 24 (Hours) 60 (Minutes) 60(Seconds) 1000(Milliseconds) and then we add the difference between the milliseconds. I added a condition to show "-" in the first data row

=If(ISNUMBER(A1),(A2-A1)*24*60*60*1000+(B2-B1),"-")

Upvotes: 1

Related Questions