Home

Excel Timestamp Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-691200%22%20slang%3D%22en-US%22%3EExcel%20Timestamp%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-691200%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20subtract%20timestamps%20in%20Excel%20that%20are%20formatted%20as%20%22day%20hour%2Fminute%2Ftime.%22%26nbsp%3B%20The%20problem%20is%20that%20Excel%20doesn't%20recognize%20it%20as%20a%20date%20or%20time%2C%20so%20I%20can't%20extract%20out%20the%20time%20calculations.%20I%20tried%20using%20the%20%22Custom%22%20format%20to%20help%20Excel%20recognize%20it%20as%20a%20day%2Ftime%20format%2C%20but%20it's%20not%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3E12%2003%3A45%3A12%20(day%20hour%3Amin%3Asec)%3C%2FP%3E%3CP%3E12%2003%3A55%3A13%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20want%20to%20subtract%20the%20first%20timestamp%20from%20the%20second%2C%20how%20do%20I%20do%20that%20to%20arrive%20at%20the%20answer%20(0%20days%2C%200%20hours%2C%2010%20min%2C%201%20sec)%3F%20I%20have%20a%20feeling%20the%20%2212%22%20is%20confusing%20the%20formula%20but%20I%20don't%20know%20how%20to%20subtract%20it%20out%2C%20as%20Excel%20doesn't%20recognize%20it%20as%20a%20date%20without%20the%20month%20and%20year.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-691200%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-691252%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Timestamp%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-691252%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F359983%22%20target%3D%22_blank%22%3E%40wangcc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20ignore%20dates%20(all%20timestamps%20are%20for%20the%20same%20date)%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DRIGHT(A2%2C8)-RIGHT(A1%2C8)%3C%2FPRE%3E%0A%3CP%3Eand%20format%20resulting%20cell%20as%20time%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20add%20days%20difference%20when%3C%2FP%3E%0A%3CPRE%3E%3DRIGHT(A2%2C8)-RIGHT(A1%2C8)%2BLEFT(A2%2C2)-LEFT(A1%2C2)%3C%2FPRE%3E%0A%3CP%3Eand%20format%20the%20result%20as%20elapsed%20time%2C%20i.e.%20%5Bhh%5D%3Amm%3Ass%3C%2FP%3E%3C%2FLINGO-BODY%3E
wangcc
Occasional Visitor

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

@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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies