PDA

View Full Version : [SOLVED:] Timestamp difference in Millisecond



justdream
01-19-2020, 03:16 AM
Dears,

Kindly help me to get excel formula that can calculate Timestamp difference in Millisecond
I've attached an example of two inputs that I would like to calculate the time duration between both of them in Millisecond

p45cal
01-19-2020, 03:55 AM
Can't see the file, don't have a 4shared account, nor facebook, nor twitter, nor Google+. Never will.

justdream
01-19-2020, 04:24 AM
Can't see the file, don't have a 4shared account, nor facebook, nor twitter, nor Google+. Never will.
No issue, I just replaced my link with attached excel file

p45cal
01-19-2020, 07:12 AM
In the attached, in cells B4:E4 are some longish formulae all including (twice) bits like:
SUMPRODUCT(B2:E2,1/{24,1440,86400,86400000})
[Those numbers are from 24 hours in a day, 1440 minutes in a day, 86400 seconds in a day and 86400000 milliseconds in a day.]
It calculates the time stamp as a fraction of a day, which is how Excel deals with time, which means you can apply direct maths on the values.
If you use helper cells (H2 & H3) you can make the formulae shorter (or apply simple arithmetic to them as in cell H4) to leave the formulae in B7:E7.
I hope your time stamp differences don't go over 24 hours (because HOUR() functions in column B might go awry).

ps. Do you receive these time stamps as separate values for hours/mins/secs/millisecs or as a single value? It would be a lot easier to deal with a single value.