Time calculation "challenge"

[Jan Kronsell]

I have run into a time calculation problem, that is more challenging, that I
tought it would be.

I have looked at Chip Pearsons site, but I have not been able to find
anything solving my specific problem, but maybe I haven't lookee good
enough.

Anyway, here goes:

in a spreadsheet I enter a StartTime ( in A1) and end EndTime ( in a B1). In
C1 i calculate the time between A1 and B1. That part works perfectly OK. Now
themy challenge is to calculate, how much time between StartTime and EndTime
lies with the interval between 6AM (06:00) and 5PM (17:00).

I tried with different formulas, and I can get each of them to work on
certain StartTimes and EndTimes, but not on other. I have trouble finding a
formula, that covers all StartTime/EndTime scenarios.

StartTime can be anything between 00:00 and 23:59. The Same goes for
EndTime.

Here are the differenct scenarios, I have:

1) StartTime after 6AM, Endtime Before 5PM. This formula does the job:
=IF(AND(A1>=(6/24),B1<=(17/24)),B1-A1)



2) StartTime before 5PM, Endtime After 5PM. This formula does the job:

=IF((17/24)-A1<0,0,(17/24)-A1)



3) StartTime after 5PM and before midnight, Endtime After 5PM. The formula
from 2) does the job.



4) StartTime after midnight, EndTime before 6AM. The formula from 2) and 3)
does not work, this one does: =IF(AND(A1>=0,A1<=(6/24)),(B1-A1))



5) StartTime after 5PM, EndTime after 6AM



6) StartTime before 5PM, EndTime after 6AM



I haven't been able how to calculate scenario 5 or 6, and I have no clue how
to put it all together in one single formula, that handles all the
scenarios.



Can anybody help?



Jan

 

[Jan Kronsell]

Maybe I shold add, that if StartTime is 16:00 (4PM) and EndTime is 07:00AM
the number I need should be 2:00.

That is, the time within the 6:00-17:00 interval on both sides of the
interval 17:00-6:00.

Jan

 

[Peo Sjoblom]

Hi Jan,

one way with start time in A1, end time in B1, time span you want to check
in A2 (06:00) and B2 (17:00)


=MOD(B1-A1,1)-(MAX(0,MIN(B1,A2)-IF(B1>A1,A1,MIN(0,A1-A2)))+MAX(0,1-MAX(B2,A1)-IF(B1>A1,1-B1,MIN(0,B2-B1))))

probably unnecessary big formula but will work



in Danish

=REST(B1-A1;1)-(MAKS(0;MIN(B1;A2)-HVIS(B1>A1;A1;MIN(0;A1-A2)))+MAKS(0;1-MAKS(B2;A1)-HVIS(B1>A1;1-B1;MIN(0;B2-B1))))

--


Regards,

Peo Sjoblom

 

[Jan Kronsell]

I will try it out then getr back to you.

Jan

Hi Jan,

one way with start time in A1, end time in B1, time span you want to check
in A2 (06:00) and B2 (17:00)


=MOD(B1-A1,1)-(MAX(0,MIN(B1,A2)-IF(B1>A1,A1,MIN(0,A1-A2)))+MAX(0,1-MAX(B2,A1
)-IF(B1>A1,1-B1,MIN(0,B2-B1))))

probably unnecessary big formula but will work



in Danish

=REST(B1-A1;1)-(MAKS(0;MIN(B1;A2)-HVIS(B1>A1;A1;MIN(0;A1-A2)))+MAKS(0;1-MAKS
(B2;A1)-HVIS(B1>A1;1-B1;MIN(0;B2-B1))))

--


Regards,

Peo Sjoblom

 

[Jan Kronsell]

Hi Peo

It workes perfectly allright. Can you explain the formula to me. I like to
understand, what Im doing.

Jan