Tinme calculations once more

[Jan Kronsell]

I have looked at Chip Pearsons site, and I have tried several things, but do
not seem to be able to get anything to work.

I have Start time in A1 and End time in B1. Starttime can be anything
between 0:00 and 23:59 and End Time the same. The difference between Start
time and end time is never more than 24 hours.

My problem is simply to calculate how much of the spend time lies with the
interval 17:00 (5 pm) and 6:00 (6 am). Examples

Start time 06:00 End time: 16:00 Spend time. 10 hours. Within Interval: 0.
Start time 14:00 End time: 22:00 Spend time. 8 hours. Within Interval: 5.
Start time 22:00 End time: 06:00 Spend time. 8 hours. Within Interval: 8.
Start time 16:00 End time: 07:00 Spend time. 15 hours. Within Interval: 13
Start time 04:00 End time: 12:00 Spend time. 8 hours. Within Interval: 2.
Start time 04:00 End time: 18:00 Spend time. 14 hours. Within Interval: 4.
Start time 00:00 End time: 23:00 Spend time. 23 hours. Within Interval: 12.
and so on

I have made formulas, that caluclate the interval correct between 5AMand
midnight and between midnight and 6AM but I can't put them together. Any
suggestions?

Jan

 

[Bob Phillips]

=(IF(B2>A2,MAX(0,TIME(6,0,0)-A2),1-MAX(A2,TIME(17,0,0)))
+IF(B2>A2,MAX(0,B2-TIME(17,0,0)),MIN(B2,TIME(6,0,0))))*24

 

[Jan Kronsell]

Thank you. That did it

Jan

=(IF(B2>A2,MAX(0,TIME(6,0,0)-A2),1-MAX(A2,TIME(17,0,0)))
+IF(B2>A2,MAX(0,B2-TIME(17,0,0)),MIN(B2,TIME(6,0,0))))*24

 

[Shane Devenshire]

Hi,

You could also use this version:

=24*IF(B1>A1,MAX(0,B1-A1-11/24),1-MAX(A1,17/24)+MIN(B1,6/24))

 

[Shane Devenshire]

Hi,

Please correct the last post to read

=24*IF(B1>A1,MAX(0,6/24-A1)+MAX(0,B1-17/24),1-MAX(A1,17/24)+MIN(B1,6/24))

 

[Jan Kronsell]

Thank you. Both solutions works fine.

Jan

Hi,

Please correct the last post to read

=24*IF(B1>A1,MAX(0,6/24-A1)+MAX(0,B1-17/24),1-MAX(A1,17/24)+MIN(B1,6/24))

 

[Jan Kronsell]

Unfortunately the solutions doens work so fine as I thought.

With Start time 17:45 and End:time 21:45 it should return 4, but both
functions actually returns, 4,75, and so on, indicating,that i fstart time
is later than 17:00 it still calculates from 17:00

Jan

 

[Jan Kronsell]

Maybe the error lies here:

MAX(0;B1-17/24) as this is only true, if A1 is before 17:00. If it is after,
it should read MAX(0;B1-A1) in stead.

Or so I think.

Jan

 

[Bob Phillips]

slight adjustment

=(IF(B2>A2,MAX(0,MIN(B2,TIME(6,0,0))-A2),1-MAX(A2,TIME(17,0,0)))
+IF(B2>A2,MAX(0,B2-MAX(A2,TIME(17,0,0))),MIN(B2,TIME(6,0,0))))*24

 

[Bob Phillips]

This should correct the other one as well

=24*IF(B2>A2,MAX(0,MIN(B2,6/24)-A2)+MAX(0,B2-MAX(A2,17/24)),1-MAX(A2,17/24)+MIN(B2,MIN(B2,6/24)))

 

[Jan Kronsell]

Thank you. It looks allright, from expeirience I think I will test it with
all possible combinations of start and end time before I say anything
I'll get back-

Jan

 

[Jan Kronsell]

Now I think I have tested all possible combinations, and it still works.
Thank you.

Jan