Forum Discussion

wangcc's avatar
wangcc
Copper Contributor
Jun 13, 2019

Excel Timestamp Formula

I'm trying to subtract timestamps in Excel that are formatted as "day hour/minute/time."  The problem is that Excel doesn't recognize it as a date or time, so I can't extract out the time calculations. I tried using the "Custom" format to help Excel recognize it as a day/time format, but it's not working.

 

Example:

12 03:45:12 (day hour:min:sec)

12 03:55:13 

 

If I want to subtract the first timestamp from the second, how do I do that to arrive at the answer (0 days, 0 hours, 10 min, 1 sec)? I have a feeling the "12" is confusing the formula but I don't know how to subtract it out, as Excel doesn't recognize it as a date without the month and year.

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    wangcc 

     

    If ignore dates (all timestamps are for the same date) that could be

    =RIGHT(A2,8)-RIGHT(A1,8)

    and format resulting cell as time

     

    If add days difference when

    =RIGHT(A2,8)-RIGHT(A1,8)+LEFT(A2,2)-LEFT(A1,2)

    and format the result as elapsed time, i.e. [hh]:mm:ss

Resources