SOLVED

How to subtract one date/time from another?

%3CLINGO-SUB%20id%3D%22lingo-sub-3482653%22%20slang%3D%22en-US%22%3EHow%20to%20subtract%20one%20date%2Ftime%20from%20another%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3482653%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20to%20subtract%20one%20date%2Ftime%20from%20another%3F%20Format%20is%20dd%3Amm%3Ayy%20hh%3Amm%3Ass.%3C%2FP%3E%3CP%3EI%20tried%20Datevalue%2C%20Timevalue%2C%20did%20not%20work%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3482653%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3482799%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20subtract%20one%20date%2Ftime%20from%20another%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3482799%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1417900%22%20target%3D%22_blank%22%3E%40gr1113%3C%2FA%3E%26nbsp%3B%20so%20let's%20say%20that%20text%20string%20is%20in%20column%20A.%26nbsp%3B%20First%20off%20is%20that%20Excel%20doesn't%20recognize%20dd%3Amm%3Ayy%20so%20you%20need%20to%20replace%20those%20%22%3A%22%20with%20%22%2F%22%20then%20you%20can%20use%26nbsp%3BDATEVALUE%20to%20convert%20to%20a%20date%20value%20that%20you%20can%20add%2Fsubtract.%26nbsp%3B%20Assuming%20it%20is%20truly%20dd%3Amm%3Ayy%26nbsp%3B%20and%20therefore%20days%201-9%20and%20months%201-9%20show%20up%20as%2001-09%20then%20you%20could%20try%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DDATEVALUE(LEFT(A1%2C2)%26amp%3B%22%2F%22%26amp%3BMID(A1%2C4%2C2)%26amp%3B%22%2F%22%26amp%3BMID(A1%2C7%2C15))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eor%20if%20it%20is%20the%20whole%20column%20you%20could%20replace%20A1%20with%20A1%3AA100%20or%20whatever%20the%20range%20is.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3486534%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20subtract%20one%20date%2Ftime%20from%20another%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3486534%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1417900%22%20target%3D%22_blank%22%3E%40gr1113%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

How to subtract one date/time from another? Format is dd:mm:yy hh:mm:ss.

I tried Datevalue, Timevalue, did not work

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@gr1113  so let's say that text string is in column A.  First off is that Excel doesn't recognize dd:mm:yy so you need to replace those ":" with "/" then you can use DATEVALUE to convert to a date value that you can add/subtract.  Assuming it is truly dd:mm:yy  and therefore days 1-9 and months 1-9 show up as 01-09 then you could try:

=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,4,2)&"/"&MID(A1,7,15))

or if it is the whole column you could replace A1 with A1:A100 or whatever the range is.

@gr1113 

Thank you for your reply.