Forum Discussion
Convert custom date format <day> <hour>:<minute>:<second> to something workable in excel?
hi Team,
looking for some help here! have a data file which has 3 columns measuring a specific time and I need to calulate the difference between them. the challenge i'm having is that the format in the file is as follows:
<day> <hour>:<minute>:<second>
I need to calculate the number of seconds between each stage, can you help???
Even if I minus the two, if stage 1 is at 3 23:55:13 and stage 2 is at 4 00:05:25 (ie #1 is before midnight and #2 is after) then i get weird negative values. sample below.
any ideas?
| Customer placed order datetime | Placed order with restaurant datetime | Driver at restaurant datetime | Delivered to consumer datetime |
| 01 02:52:12 | 01 03:00:25 | 01 03:08:09 | 01 03:35:20 |
| 13 15:58:57 | 13 17:56:45 | 13 18:24:39 | 13 18:57:01 |
| 17 19:02:37 | 17 19:12:51 | 17 19:19:59 | 17 19:31:09 |
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.
11 Replies
That is just format, but what are the values - texts as in your sample or datetime formatted as
d hh:mm:ss ?
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.
OK so apparently the HTML didn't like my message content...
the structure is as follows:
"<day> <hour>:<minute>:<second>"
