+ Reply to Thread
Results 1 to 3 of 3

How can I take break times from enhanced rate and overtime if applicable on a timesheet?

  1. #1
    Registered User
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    8

    How can I take break times from enhanced rate and overtime if applicable on a timesheet?

    I'm trying to calculate worked hours with a single break time removed

    The breaktime needs to be removed from the enhanced rate hours, this is between 6pm and 6am, if there is no time in the enhanced rate then it takes the time off the normal hours, but it may be that the break is 45 minutes long but 30 minutes of work has been done in the higher rate, so it wouls actually be 15 minutes from the normal rate.


    For hours between 6am and 6pm I'm using

    =24*((B5>C5)*MEDIAN(0,C5-1/4,3/4-1/4)+MAX(0,MIN(3/4,C5+(B5>C5))-MAX(1/4,B5)))

    For hours before 6am or after 6pm im using

    =24*((B5>C5)*MIN(C5,1/4)+MAX(0,MIN(1/4,C5+(B5>C5))-B5))+24*((B5>C5)*MEDIAN(0,B5-3/4,1/4)+MAX(0,MIN(1,C5+(B5>C5))-MAX(3/4,B5)))

    For overtime (any working time over 9 hours)

    =IF((C5-B5)*24>9,((C5-B5)*24-9),0)

    Break time is in D5 and can be anything from 15 minutes to 60 minutes

    Please does anyone know how to subtract the break time.

    Thanks

    Mak

  2. #2
    Registered User
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: How can I take break times from enhanced rate and overtime if applicable on a timeshee

    Anyone have a solution please?

    Mak

  3. #3
    Registered User
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: How can I take break times from enhanced rate and overtime if applicable on a timeshee

    Hi

    I'm not sure I explained it properly

    Let me try again, along with the worksheet

    I'm trying to calculate worked hours with a single break time removed in Excel 2003

    The breaktime needs to be removed from the enhanced rate hours, this is between 6pm and 6am, if there is no time in the enhanced rate then it takes the time off the normal hours, but it may be that the break is 45 minutes long but 30 minutes of work has been done in the higher rate, so it wouls actually be 15 minutes from the normal rate.

    B5 contains start time and C5 contains end time

    Cell E5 contains the following formual to calculate hours between 6am and 6pm

    =24*((B5>C5)*MEDIAN(0,C5-1/4,3/4-1/4)+MAX(0,MIN(3/4,C5+(B5>C5))-MAX(1/4,B5)))

    Cell F5 contains the following formual to calculate hours before 6am or after 6pm

    =24*((B5>C5)*MIN(C5,1/4)+MAX(0,MIN(1/4,C5+(B5>C5))-B5))+24*((B5>C5)*MEDIAN(0,B5-3/4,1/4)+MAX(0,MIN(1,C5+(B5>C5))-MAX(3/4,B5)))

    Cell G5 contains the following formula to calculate overtime (any working time over 9 hours)

    =IF((C5-B5)*24>9,((C5-B5)*24-9),0)

    I now need to deduct the break time entered in D5 (this can be anything from 15 minutes to 60 minutes) from the highest rate, so before 6am and after 6pm is enhanced rate, so the break time needs to be deducted from this, however, this may be anything from 15 minutes up, if less than the break time the remainder would need to be deducted from the 6am to 6pm standard rate time.

    Sometimes no enhanced rate is worked so the whole of the break time would be deducted from the standard rate time.

    Please does anyone know how to I can acheive this please?

    Thanks

    Mak
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Timesheet calculation for overtime and double overtime
    By eortega in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-29-2013, 03:28 PM
  2. Calculating Day Rate and Night Rate Hours on a Timesheet
    By Hahnium in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-21-2013, 08:18 AM
  3. Overtime on Timesheet
    By MEMEM in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-03-2010, 09:12 PM
  4. Timesheet / Overtime rate calculation
    By mobiay in forum Excel General
    Replies: 3
    Last Post: 03-19-2008, 04:42 PM
  5. Timesheet without break times
    By village_idiot in forum Excel General
    Replies: 5
    Last Post: 05-03-2007, 11:01 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1