Sep 18 2019 05:31 AM
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 |
Sep 18 2019 05:32 AM
OK so apparently the HTML didn't like my message content...
the structure is as follows:
"<day> <hour>:<minute>:<second>"
Sep 18 2019 08:50 AM
That is just format, but what are the values - texts as in your sample or datetime formatted as
d hh:mm:ss ?
Sep 18 2019 08:58 AM
thanks @Sergei Baklan - if i understand correctly, it is date time in the format you suggested d hh:mm:ss
Sep 18 2019 09:18 AM
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.
Sep 18 2019 09:25 AM
thanks @Sergei Baklan not sure what im doing wrong but get an error message.
sample file attached...
Sep 18 2019 10:12 AM
SolutionYou 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.
Feb 26 2021 06:58 AM
How to deal with negative values in this case? especially when there 24hrs overlap ?
Feb 26 2021 07:03 AM
Feb 26 2021 07:08 AM
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
Feb 26 2021 09:50 AM
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.
Feb 26 2021 11:14 AM
Thanks got it
Sep 18 2019 10:12 AM
SolutionYou 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.