Forum Discussion

zachary_king355's avatar
zachary_king355
Copper Contributor
Sep 18, 2019
Solved

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 form...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 18, 2019

    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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 26, 2021
Which exactly case do you mean? The question was about converting text to datetime.
Dezmaster2020's avatar
Dezmaster2020
Copper Contributor
Feb 26, 2021

SergeiBaklan 

Yes. I followed your recommendations but I have some instances where I have negative values in my calculation.

You will find some examples in the columns highlighted in red (calculated) in my dataset

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 26, 2021

    Dezmaster2020 

    Thank you for the file, but it's logic is not complete. We don't know what is the last day of the month, 31 as in Jan, or 28 as in Feb this year, or 29 as in Feb of the leap year. Other words, we need year and month in addition.

     

    With current information we may assume that if restaurant datetime is on next month compare to  customer datetime then customer datetime is always on the last day of the month. With this formula could be

    =IFERROR(  (  IF( LEFT(B2,2)<LEFT(A2,2), LEFT(B2,2), LEFT(B2,2)-LEFT(A2,2) )+(TIMEVALUE(RIGHT(B2,8))-TIMEVALUE(RIGHT(A2,8))) )*24*60, "")

    But that gives wrong value if, for example customer place order on Feb 27 and restaurant - on Mar 01.

    That could be the case, for example in row 16666 we have

    28 06:14:16 30 00:57:01

    So, to do reliable calculations year/month are required.

    Please check second sheet in attached file. 

Resources