How to subtract one date/time from another?

[gr1113]

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

I tried Datevalue, Timevalue, did not work

[#2 · Accepted 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.

[#3]

@gr1113 

Thank you for your reply.