Tinme calculations once more


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
 
Ad

Advertisements

B

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
 
J

Jan Kronsell

Thank you. That did it :)

Jan
Bob said:
=(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
 
S

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))
 
S

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))
 
J

Jan Kronsell

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))
 
Ad

Advertisements

J

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
 
J

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
 
B

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
 
B

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)))
 
J

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
 
Ad

Advertisements

J

Jan Kronsell

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

Jan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top