Forum Discussion
How to subtract one date/time from another?
How to subtract one date/time from another? Format is dd:mm:yy hh:mm:ss.
I tried Datevalue, Timevalue, did not work
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.
2 Replies
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.
