How can I work out how many hours I get premium rate?

[HappyTrucker]

Hi, this is my first post here, so be gentle

I use a spreadsheet to track my working time. The way our wages are
calculated alter, depending on the time of day. The basic hourly rate
is incremented slightly as you go into 'unsociable hours'. I have the
following formula for working out how many hours attract a premium
after 10pm, based on starting mid afternoon:

=IF(B6="","",(IF(B6<=(LEFT(B5,5)+"22:00:00"),1-1,((((B6-(LEFT(B5,5)+"22:00:00"))-INT(B6-(LEFT(B5,5)+"22:00:00"))))*24)-0.75)))

This is broken down as follows:

If 'Finish Time (B6)' is blank, be blank, if 'Finish Time (B6)' is less
than or equal to 'Start Date (B5 left hand 5 characters) + 22:00' then
be '0'. Otherwise, work out how many hours I worked after 10pm, less
the unpaid meal break. Hope that makes sense.

Now, I've moved to night shifts and am having difficulty transforming
the code to cover this. We attract a premium rate between the hours of
10pm and 6am, so I am trying to modify it to show me how many hours I
worked between these times.

As an example: I start work at 22:00 and work until 08:00. This is a 10
hour shift, but only the hours up to 6am count for the formula. So the
number I need would be 8 (10pm til 6am). If my shift is 9pm til 8am
then this is an 11 hour shift, and the figure I need would be 8 again.
If I start at 11pm, the figure I am looking for would be 7 (11pm until
6am = 7 hours).

I've tried a couple of mutations but I keep either getting it working
out everything up to 6am, whether the shift started before 10pm or not,
or always coming up with 8.00.

I'm aiming for a formula that gives:

If Finish Time is blank, be blank, If Start Time is after or equal to
10pm AND finish time is after 6am then what is 6am minus Start Time, or
if Start Time is after or equal to 10pm and Finish Time is before or
equal to 6am what is Finish Time - Start Time, if Start Time is before
10pm and Finish Time is before 6am what is Finish Time - 10pm,
otherwise all other options equate to 6am - 10pm = 8hours.

If it helps, the start and finish times are entered as dd/mm/yyyy hh:mm
and formatted to display hh:mm

Any help will be appreciated - it's driving me scatty now

 

[HappyTrucker]

OK, I think I've sorted it. Well it seems to work anyway. I don't know
whether I stumped you all, or it just wasn't clear enough. Or maybe the
problem wasn't interesting enough. Anyway, if anyone's interested,
here's what I came up with eventually. And my God is it a loooong one:

=IF(B15="","",IF(AND((B14<(LEFT(B14,5)+"22:00")),(B15<=(LEFT(B15,5)+"06:00"))),((B15-(LEFT(B14,5)+"22:00"))*24),IF(AND((B14>=(LEFT(B14,5)+"22:00")),
(B15<=(LEFT(B15,5)+"06:00"))),(B15-B14)*24,IF(AND((B14>(LEFT(B14,5)+"22:00")),(B15>=(LEFT(B15,5)+"06:00"))),(((LEFT(B15,5)+"06:00")-B14)*24),8))))

 

[Biff]

Hi!

Slightly shorter....

A1 = Date/Time in
B1 = Date/Time out

=IF(B1="","",IF(MOD(A1,1)<MOD(B1,1),MIN(MIN(MOD(B1,1),6/24)-MOD(A1,1),6/24),IF(MOD(A1,1)<22/24,2/24+MIN(MOD(B1,1),6/24),1-MOD(A1,1)+MIN(MOD(B1,1),6/24)))*24)

Note: Discovered rounding discrepancies during testing but they do not
effect the correctness of the results. For example:

A1 = 8/7/2005 10:00 PM

=MOD(A1,1) = 0.91666666666424100000

The time value: 10:00 PM is equivalent to 22/24 as used in the formula.
However:

22/24 = 0.91666666666666700000

Biff

"HappyTrucker" <[email protected]>
wrote in message
news:[email protected]...

 

[Biff]

Also note:

Not reliable if the time span is greater than 24 hrs.

Biff

 

[Biff]

Discovered a bug.....

Try this:

=IF(B1="","",IF(DAY(B1)=DAY(A1),MOD(B1,1)-MAX(MOD(A1,1),22/24),IF(MOD(A1,1)<MOD(B1,1),MIN(MIN(MOD(B1,1),6/24)-MOD(A1,1),6/24),IF(MOD(A1,1)<22/24,2/24+MIN(MOD(B1,1),6/24),1-MOD(A1,1)+MIN(MOD(B1,1),6/24))))*24)

Biff