Subtract DateTime in this format
C2 = Start date = 9/16/2018 12:41:02
B2 = End date = 9/18/2018 15:37:12Using 24 hour format
Tried using =TIMEVALUE(RIGHT(C2,8))-TIMEVALUE(RIGHT(B2,8)), it wouldn't work when the result supposed to be over 1 day
Expected results = 2 days 02:56:10
Is it possible to subtract this in Excel?
I want it to be =C2-B2
2 Answers
=INT(C2-C1)&" days " & TEXT(C2-C1, "h"" hrs ""m"" mints """)How it works:
- TEXT function in this formula applies a custom
number format for
Hours and Minutesto a value created by subtracting theEnd Datefrom theStart Date. - INT function,strips off the integer
portion of the
End Date,minustheStart Date.
if you have many values as End & Start Dates then this formula can be used.
=INT(SUMPRODUCT(C5:C9-B5:B9))&" days "&TEXT(SUMPRODUCT(C5:C9-B5:B9),"h"" hrs ""m"" mins """)Adjust cell references in the formula as needed.
9So i have look for some help and able to get the answer,
it can't subtract as the format that i exported out is not recognized by excel
So i used this formula to convert all the cell into Date/Time with DD/MM/YYYY HH:MM:SS from MM/DD/YYYY HH:MM:SS
=IF(ISERR(VALUE(MID(B2,3,2))),IF(ISERR(VALUE(MID(B2,3,1))),IF(ISERR(VALUE(MID(B2,4,2))),VALUE(MID(B2,4,1)),VALUE(MID(B2,4,2))),VALUE(MID(B2,3,1))),VALUE(MID(B2,3,2)))&"/"&IF(ISERR(VALUE(LEFT(B2,2))),VALUE(LEFT(B2,1)),VALUE(LEFT(B2,2)))&"/"&MID(B2,LEN(B2)-12,4)&" "&RIGHT(B2,8)
and able to use subtractions after that~
Would welcome for more simple answer for doing this.
Thanks for those who provided solutions for my questions.
