Forum Discussion
Convert custom date format <day> <hour>:<minute>:<second> to something workable in excel?
You have texts, not datetime (which is actually an number in Excel). To receive the difference in seconds you need to parse these texts like
=IFERROR((LEFT(D5,2)-LEFT(C5,2))+RIGHT(D5,8)-RIGHT(C5,8),"")and apply to the resulting cell [ss] custom format.
IFERROR returns empty string if you have error in result, that happens if one of the texts is empty.
Alternatively you may keep General format and convert result to seconds as
=IFERROR( (LEFT(D5,2)-LEFT(C5,2)+RIGHT(D5,8)-RIGHT(C5,8) )*60*60*24,"")Please check in attached file.
thanks SergeiBaklan - if i understand correctly, it is date time in the format you suggested d hh:mm:ss
If that's datetime formatted as here
you shall have correct result in seconds, formatting it as [ss].
If not, perhaps you may attach sample file.
You have texts, not datetime (which is actually an number in Excel). To receive the difference in seconds you need to parse these texts like
=IFERROR((LEFT(D5,2)-LEFT(C5,2))+RIGHT(D5,8)-RIGHT(C5,8),"")and apply to the resulting cell [ss] custom format.
IFERROR returns empty string if you have error in result, that happens if one of the texts is empty.
Alternatively you may keep General format and convert result to seconds as
=IFERROR( (LEFT(D5,2)-LEFT(C5,2)+RIGHT(D5,8)-RIGHT(C5,8) )*60*60*24,"")Please check in attached file.
How to deal with negative values in this case? especially when there 24hrs overlap ?
