Tinme within intervals

[jkrons]

I have to do some timecalculations between to timevalues. See the
examples below.

Get to work Leave Work 8-20 20-8
2/2/10 08:00 2/2/10 15:30 = 7,5 - 0
3/2/10 15:30 4/2/10 08:30 = 5 - 12
5/2/10 08:00 6/2/10 08:30 = 12,5 - 12

Other combinations are possible as well, but never more than 24,5
hours of work in a row.

I like a (two) formulas, that can calculate the time with in interval
between 8:00 and 20:00 and between 20:00 and 8:00.

Jan

 

[Bernard Liengme]

The hours for column * - 20 can be obtained from
=IF(AND(DAY(B2)=DAY(A2),TIME(HOUR(B2),MINUTE(B2),0)<=TIME(20,0,0)),(B2-A2)*24,(TIME(20,30,0)-TIME(HOUR(A2),MINUTE(A2),0))*24)
The first one is in C2
And the 20 - 8 are computed in D2 with
=(B2-A2)*24-C2
You did not give complete info, so you many need to make minor adjustments
For example, is this correct for you
05/02/2010 08:00 05/02/2010 20:45 12.5 0.25
best wishes

 

[jkrons]

Your formulas gives me the correct results for case two and three,
but not in case 1. Here they returns -7,5 and 15 instead of 7,5 and 0.

As to 05/02/2010 08:00 05/02/2010 20:45
12.5 0.25

Yes is possible, but should return


05/02/2010 08:00 06/02/2010 20:45 12 0.75

There can never be m,ore than 12 hours between 8 and 20 if the date
is the same. This would exeed 12 though

05/02/2010 08:00 05/02/2010 20:45 24 0.75


Actually every combination of getting to work one day, and leaving the
same or next day is possible like for instance

05/02/2010 00:30 06/02/2010 23:30 24 23

Jan

 

[Bernard Liengme]

With
=IF(AND(DAY(B2)=DAY(A2),TIME(HOUR(B2),MINUTE(B2),0)<=TIME(20,0,0)),(B2-A2)*24,(TIME(20,30,0)-TIME(HOUR(A2),MINUTE(A2),0))*24)
these are the results I get
02/02/2010 08:00 02/02/2010 15:30 7.5 0
03/02/2010 15:30 04/02/2010 08:30 5 12
05/02/2010 08:00 06/02/2010 08:30 12.5 12
05/02/2010 08:00 05/02/2010 20:45 12.5 0.25

To fix the last example to suit you need, change formula to
=IF(AND(DAY(B2)=DAY(A2),TIME(HOUR(B2),MINUTE(B2),0)<=TIME(20,0,0)),(B2-A2)*24,(TIME(20,0,0)-TIME(HOUR(A2),MINUTE(A2),0))*24)

Note that TIME(20,30,0) is not TIME(20,0,0)

go to leave 8 - 20 20 - 8
02/02/2010 08:00 02/02/2010 15:30 7.5 0
03/02/2010 15:30 04/02/2010 08:30 4.5 12.5
05/02/2010 08:00 06/02/2010 08:30 12 12.5
05/02/2010 08:00 05/02/2010 20:45 12 0.75

I am taking the liberty of sending you a file by private email
best wishes

 

[jkrons]

Thank you. I haven't recieved your file yet, but I will look into your
solution tomorrow, as I've been busy all day today.

Jan

 

[Jan Kronsell]

Thank you. That suited my purpose fine.

Jan

Thank you. I haven't recieved your file yet, but I will look into your
solution tomorrow, as I've been busy all day today.

Jan