Date and Time format with Range calculation

[Wong]

Hi,

Need help on date and time format calculation. I need to calculate the difference between 2 dates with time then determine the time difference fall in which range.

e.g.
07/03/2017 17:41:35 (A2) and 11/03/2017 22:02:52 (B2)
Difference = 100 Hours 21 Mins 17 Secs
100:21:17 should be in range "More than 24 Hours"

The difference can be ranged from 00:00:01 to more than 100:00:00.

The formula that I used unable to determine the correct range. Attached my sample file. Appreciate your help.

 

[Wong]

Attached the sample file. Thanks

 

[vletm]

Wong
I modified C-column too...

 

[Wong]

Hi vletm,

Thanks for your help. However, I'm still facing problem (Row 3 and 4).
Both with 0:01:00 but fall into 2 different ranges.
Any idea?

 

[vletm]

Wong - good timing
There are missing one seconds from all limits (E-col); except the 1st one.

 

[Wong]

I did try to change the limits (E-col) like what you did. After this change, the problem now is with 0:01:01 with 2 different ranges.

 

[vletm]

Wong - those time values are always interesting ...

 

[Wong]

vletm, time is rebellious...another problem with >5-24 Hours.

 

[vletm]

Wong
I didn't like to use this method, but no way...
What would be next challenge?

 

[bosco_yip]

vletm, time is rebellious...another problem with >5-24 Hours.

1] Time Ranges table set-up in G17:H25

2] In F2, formula copy down :

=ROUND(B2-A2,10)

3] In F2, formula copy down :

=LOOKUP(F2,H$17:H$25,G$17:G$25)

Regards
Bosco

 

[vletm]

bosco_yip ... yes ...
lock is lock
I noticed that ...
=ROUND(B2-A2,10)
=>
=ROUND(B2-A2,5)
5 seems to be enough for num_digits
thanks.

 

[Wong]

Many thanks vletm and bosco_yip.
I noticed that if use:
1. ROUND(B2-A2,10), problem at 24:00:01
2. ROUND(B2-A2,5), problem at 1:00:01

So I use (ROUND(C2*84600),0). This seems able to solve above 2 issues.

 

[vletm]

Wong
no need those rounds there...
ReCheck #9's Book5.xlsx E-column
Seconds are seconds ...