Tinme calculations once more

  • Thread starter Thread starter Jan Kronsell
  • Start date Start date
J

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
 
=(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
 
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))
 
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))
 
Thank you. Both solutions works fine.

Jan

Shane said:
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))
Click to expand...
 
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
 
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
 
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
 
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)))
 
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
 
Now I think I have tested all possible combinations, and it still works.
Thank you.

Jan
 
Back
Top