SOLVED
Home

Convert custom date format <day> <hour>:<minute>:<second> to something workable in excel?

%3CLINGO-SUB%20id%3D%22lingo-sub-860944%22%20slang%3D%22en-US%22%3EConvert%20custom%20date%20format%20%3CDAY%3E%20%3CHOUR%3E%3A%3CMINUTE%3E%3A%3CSECOND%3E%20to%20something%20workable%20in%20excel%3F%3C%2FSECOND%3E%3CLINGO-BODY%20id%3D%22lingo-body-860944%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%20Team%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elooking%20for%20some%20help%20here!%26nbsp%3B%20have%20a%20data%20file%20which%20has%203%20columns%20measuring%20a%20specific%20time%20and%20I%20need%20to%20calulate%20the%20difference%20between%20them.%26nbsp%3B%20the%20challenge%20i'm%20having%20is%20that%20the%20format%20in%20the%20file%20is%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CDAY%3E%20%3CHOUR%3E%3A%3CMINUTE%3E%3A%3CSECOND%3E%3C%2FSECOND%3E%3C%2FMINUTE%3E%3C%2FHOUR%3E%3C%2FDAY%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20need%20to%20calculate%20the%20number%20of%20seconds%20between%20each%20stage%2C%20can%20you%20help%3F%3F%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EEven%20if%20I%20minus%20the%20two%2C%20if%20stage%201%20is%20at%203%2023%3A55%3A13%20and%20stage%202%20is%20at%204%2000%3A05%3A25%20(ie%20%231%20is%20before%20midnight%20and%20%232%20is%20after)%20then%20i%20get%20weird%20negative%20values.%26nbsp%3B%20sample%20below.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Eany%20ideas%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3ECustomer%20placed%20order%20datetime%3C%2FTD%3E%3CTD%3EPlaced%20order%20with%20restaurant%20datetime%3C%2FTD%3E%3CTD%3EDriver%20at%20restaurant%20datetime%3C%2FTD%3E%3CTD%3EDelivered%20to%20consumer%20datetime%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01%2002%3A52%3A12%3C%2FTD%3E%3CTD%3E01%2003%3A00%3A25%3C%2FTD%3E%3CTD%3E01%2003%3A08%3A09%3C%2FTD%3E%3CTD%3E01%2003%3A35%3A20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E13%2015%3A58%3A57%3C%2FTD%3E%3CTD%3E13%2017%3A56%3A45%3C%2FTD%3E%3CTD%3E13%2018%3A24%3A39%3C%2FTD%3E%3CTD%3E13%2018%3A57%3A01%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E17%2019%3A02%3A37%3C%2FTD%3E%3CTD%3E17%2019%3A12%3A51%3C%2FTD%3E%3CTD%3E17%2019%3A19%3A59%3C%2FTD%3E%3CTD%3E17%2019%3A31%3A09%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-860944%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FMINUTE%3E%3C%2FHOUR%3E%3C%2FDAY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860954%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20custom%20date%20format%20%3CDAY%3E%20%3CHOUR%3E%3A%3CMINUTE%3E%3A%3CSECOND%3E%20to%20something%20w%3C%2FSECOND%3E%3CLINGO-BODY%20id%3D%22lingo-body-860954%22%20slang%3D%22en-US%22%3E%3CP%3EOK%20so%20apparently%20the%20HTML%20didn't%20like%20my%20message%20content...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20structure%20is%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%22%3CDAY%3E%20%3CHOUR%3E%3A%3CMINUTE%3E%3A%3CSECOND%3E%22%3C%2FSECOND%3E%3C%2FMINUTE%3E%3C%2FHOUR%3E%3C%2FDAY%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FMINUTE%3E%3C%2FHOUR%3E%3C%2FDAY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-861383%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20custom%20date%20format%20%3CDAY%3E%20%3CHOUR%3E%3A%3CMINUTE%3E%3A%3CSECOND%3E%20to%20something%20w%3C%2FSECOND%3E%3CLINGO-BODY%20id%3D%22lingo-body-861383%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F411015%22%20target%3D%22_blank%22%3E%40zachary_king355%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20is%20just%20format%2C%20but%20what%20are%20the%20values%20-%20texts%20as%20in%20your%20sample%20or%20datetime%20formatted%20as%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3CSTRONG%3Ed%20hh%3Amm%3Ass%3C%2FSTRONG%3E%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FMINUTE%3E%3C%2FHOUR%3E%3C%2FDAY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-861408%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20custom%20date%20format%20%3CDAY%3E%20%3CHOUR%3E%3A%3CMINUTE%3E%3A%3CSECOND%3E%20to%20something%20w%3C%2FSECOND%3E%3CLINGO-BODY%20id%3D%22lingo-body-861408%22%20slang%3D%22en-US%22%3E%3CP%3Ethanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B-%20if%20i%20understand%20correctly%2C%20it%20is%20date%20time%20in%20the%20format%20you%20suggested%20d%20hh%3Amm%3Ass%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FMINUTE%3E%3C%2FHOUR%3E%3C%2FDAY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-861447%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20custom%20date%20format%20%3CDAY%3E%20%3CHOUR%3E%3A%3CMINUTE%3E%3A%3CSECOND%3E%20to%20something%20w%3C%2FSECOND%3E%3CLINGO-BODY%20id%3D%22lingo-body-861447%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F411015%22%20target%3D%22_blank%22%3E%40zachary_king355%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20that's%20datetime%20formatted%20as%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20435px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132382i7ABD7147E10E9016%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20shall%20have%20correct%20result%20in%20seconds%2C%20formatting%20it%20as%20%5Bss%5D.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20not%2C%20perhaps%20you%20may%20attach%20sample%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FMINUTE%3E%3C%2FHOUR%3E%3C%2FDAY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-861456%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20custom%20date%20format%20%3CDAY%3E%20%3CHOUR%3E%3A%3CMINUTE%3E%3A%3CSECOND%3E%20to%20something%20w%3C%2FSECOND%3E%3CLINGO-BODY%20id%3D%22lingo-body-861456%22%20slang%3D%22en-US%22%3E%3CP%3Ethanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bnot%20sure%20what%20im%20doing%20wrong%20but%20get%20an%20error%20message.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esample%20file%20attached...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FMINUTE%3E%3C%2FHOUR%3E%3C%2FDAY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-861571%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20custom%20date%20format%20%3CDAY%3E%20%3CHOUR%3E%3A%3CMINUTE%3E%3A%3CSECOND%3E%20to%20something%20w%3C%2FSECOND%3E%3CLINGO-BODY%20id%3D%22lingo-body-861571%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F411015%22%20target%3D%22_blank%22%3E%40zachary_king355%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20have%20texts%2C%20not%20datetime%20(which%20is%20actually%20an%20number%20in%20Excel).%20To%20receive%20the%20difference%20in%20seconds%20you%20need%20to%20parse%20these%20texts%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR((LEFT(D5%2C2)-LEFT(C5%2C2))%2BRIGHT(D5%2C8)-RIGHT(C5%2C8)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20apply%20to%20the%20resulting%20cell%20%3CSTRONG%3E%5Bss%5D%3C%2FSTRONG%3E%20custom%20format.%3C%2FP%3E%0A%3CP%3EIFERROR%20returns%20empty%20string%20if%20you%20have%20error%20in%20result%2C%20that%20happens%20if%20one%20of%20the%20texts%20is%20empty.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlternatively%20you%20may%20keep%20General%20format%20and%20convert%20result%20to%20seconds%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(%20(LEFT(D5%2C2)-LEFT(C5%2C2)%2BRIGHT(D5%2C8)-RIGHT(C5%2C8)%20)*60*60*24%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FMINUTE%3E%3C%2FHOUR%3E%3C%2FDAY%3E%3C%2FLINGO-SUB%3E
Highlighted
zachary_king355
New Contributor

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 datetimePlaced order with restaurant datetimeDriver at restaurant datetimeDelivered to consumer datetime
01 02:52:1201 03:00:2501 03:08:0901 03:35:20
13 15:58:5713 17:56:4513 18:24:3913 18:57:01
17 19:02:3717 19:12:5117 19:19:5917 19:31:09

 

 

 

6 Replies
Highlighted

OK so apparently the HTML didn't like my message content...

 

the structure is as follows:

 

 "<day> <hour>:<minute>:<second>"

Highlighted

@zachary_king355 

That is just format, but what are the values - texts as in your sample or datetime formatted as

 d hh:mm:ss ?

Highlighted

thanks @Sergei Baklan - if i understand correctly, it is date time in the format you suggested d hh:mm:ss

Highlighted

@zachary_king355 

If that's datetime formatted as here

image.png

you shall have correct result in seconds, formatting it as [ss].

 

If not, perhaps you may attach sample file.

Highlighted

thanks @Sergei Baklan not sure what im doing wrong but get an error message.

 

sample file attached...

Highlighted
Solution

@zachary_king355 

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.

Related Conversations
IF statements and conditional formatting
clare1981 in Excel on
1 Replies
Reverse numbers and characters in worksheet
David_Gerrior in Excel on
1 Replies
Sumifs
Jsbluemoon82 in Excel on
3 Replies
Excel formula similiar to texjoin
Carlo74 in Excel on
0 Replies
everything is black and white in excel
Gold4trees in Excel on
2 Replies