+ Reply to Thread
Results 1 to 53 of 53

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

  1. 08-06-2005, 04:34 PM #1
    HappyTrucker
    HappyTrucker is offline
    Registered User
    Join Date
    08-06-2005
    Posts
    5

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

    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
    Reply With QuoteRegister To Reply
  2. 08-06-2005, 06:12 PM #2
    HappyTrucker
    HappyTrucker is offline
    Registered User
    Join Date
    08-06-2005
    Posts
    5
    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))))

    Reply With QuoteRegister To Reply
  3. 08-07-2005, 01:05 AM #3
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  4. 08-07-2005, 02:05 AM #4
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  5. 08-07-2005, 03:05 AM #5
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  6. 09-05-2005, 10:05 PM #6
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  7. 09-05-2005, 10:05 PM #7
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  8. 09-05-2005, 10:05 PM #8
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  9. 09-05-2005, 11:05 PM #9
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  10. 09-05-2005, 11:05 PM #10
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  11. 09-05-2005, 11:05 PM #11
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  12. 09-06-2005, 12:05 AM #12
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  13. 09-06-2005, 12:05 AM #13
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  14. 09-06-2005, 12:05 AM #14
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  15. 09-06-2005, 01:05 AM #15
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  16. 09-06-2005, 01:05 AM #16
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  17. 09-06-2005, 01:05 AM #17
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  18. 09-06-2005, 02:05 AM #18
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  19. 09-06-2005, 02:05 AM #19
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  20. 09-06-2005, 02:05 AM #20
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  21. 09-06-2005, 03:05 AM #21
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  22. 09-06-2005, 03:05 AM #22
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  23. 09-06-2005, 03:05 AM #23
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  24. 09-06-2005, 04:05 AM #24
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  25. 09-06-2005, 04:05 AM #25
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  26. 09-06-2005, 04:05 AM #26
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  27. 09-06-2005, 06:05 AM #27
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  28. 09-06-2005, 06:05 AM #28
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  29. 09-06-2005, 06:05 AM #29
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  30. 09-06-2005, 07:05 AM #30
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  31. 09-06-2005, 07:05 AM #31
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  32. 09-06-2005, 07:05 AM #32
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  33. 09-06-2005, 10:05 AM #33
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  34. 09-06-2005, 10:05 AM #34
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  35. 09-06-2005, 10:05 AM #35
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  36. 09-06-2005, 11:05 AM #36
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  37. 09-06-2005, 11:05 AM #37
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  38. 09-06-2005, 11:05 AM #38
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  39. 09-06-2005, 12:05 PM #39
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  40. 09-06-2005, 12:05 PM #40
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  41. 09-06-2005, 12:05 PM #41
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  42. 09-06-2005, 02:05 PM #42
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  43. 09-06-2005, 02:05 PM #43
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  44. 09-06-2005, 02:05 PM #44
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  45. 09-06-2005, 03:05 PM #45
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  46. 09-06-2005, 03:05 PM #46
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  47. 09-06-2005, 03:05 PM #47
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  48. 09-06-2005, 06:05 PM #48
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
  49. 09-06-2005, 06:05 PM #49
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  50. 09-06-2005, 06:05 PM #50
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  51. 09-06-2005, 07:05 PM #51
    Biff
    Guest

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

    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

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  52. 09-06-2005, 07:05 PM #52
    Biff
    Guest

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

    Also note:

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

    Biff

    "Biff" <[email protected]> wrote in message
    news:[email protected]...
    > 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]...
    >>
    >> 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))))
    >>
    >>
    >>
    >>
    >> --
    >> HappyTrucker
    >> ------------------------------------------------------------------------
    >> HappyTrucker's Profile:
    >> http://www.excelforum.com/member.php...o&userid=25997
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=393601
    >>
    >
    >


    Reply With QuoteRegister To Reply
  53. 09-06-2005, 07:05 PM #53
    Biff
    Guest

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

    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]...
    >
    > 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))))
    >
    >
    >
    >
    > --
    > HappyTrucker
    > ------------------------------------------------------------------------
    > HappyTrucker's Profile:
    > http://www.excelforum.com/member.php...o&userid=25997
    > View this thread: http://www.excelforum.com/showthread...hreadid=393601
    >


    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1