Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how to find time difference between timestamps to a millisecond?
I have two columns which contain timestamps in the format of yyyy-mm-dd
hh:mm:ss:msmsms (the last three digits are the milliseconds). I need to find the difference of the timestamps in ss:msmsms format. I am trying to implement a VB function to do this as there is no predefined function available. Can anyone please assist? Thanks! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how to find time difference between timestamps to a millisecond?
If you have date/time in exactly that format (ie 05 for fifth month not just
5) then this formula will convert your data to Excel's date/time format =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID (A1,12,2),MID(A1,15,2),MID(A1,18,2))+RIGHT(A1,3)/1000/60/60/24 You will need to format the cell with yyyy/mm/dd hh:mm:ss.000 In my test I changed 2006-06-10 05:45:15:349 to 2006/06/10 05:45:15.349 To get difference, I used =ROUND((F2-F1)*24*60*60,3) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "sp" wrote in message ... I have two columns which contain timestamps in the format of yyyy-mm-dd hh:mm:ss:msmsms (the last three digits are the milliseconds). I need to find the difference of the timestamps in ss:msmsms format. I am trying to implement a VB function to do this as there is no predefined function available. Can anyone please assist? Thanks! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how to find time difference between timestamps to a millisecon
Thank you!
"Bernard Liengme" wrote: If you have date/time in exactly that format (ie 05 for fifth month not just 5) then this formula will convert your data to Excel's date/time format =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID (A1,12,2),MID(A1,15,2),MID(A1,18,2))+RIGHT(A1,3)/1000/60/60/24 You will need to format the cell with yyyy/mm/dd hh:mm:ss.000 In my test I changed 2006-06-10 05:45:15:349 to 2006/06/10 05:45:15.349 To get difference, I used =ROUND((F2-F1)*24*60*60,3) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "sp" wrote in message ... I have two columns which contain timestamps in the format of yyyy-mm-dd hh:mm:ss:msmsms (the last three digits are the milliseconds). I need to find the difference of the timestamps in ss:msmsms format. I am trying to implement a VB function to do this as there is no predefined function available. Can anyone please assist? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time difference | Excel Worksheet Functions | |||
Calculating Time difference based on a condition | Excel Discussion (Misc queries) | |||
calculate negative or positve difference in time | Excel Discussion (Misc queries) | |||
Calculating time difference | Excel Discussion (Misc queries) | |||
What is the formula for getting time difference e.g. ("4 hrs 15 m. | Charts and Charting in Excel |