+ Reply to Thread
Results 1 to 8 of 8

Calculating Day Rate and Night Rate Hours on a Timesheet

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

    Calculating Day Rate and Night Rate Hours on a Timesheet

    Hello everybody

    I'm new to the forum and a fairly basic Excel user so would really appreciate some advice on a problem I'm having with a timesheet.

    As it stands now, my timesheet will accurately calculate total hours worked over any particular shift, even those spanning two days. However, I'd also like to include a calculation for my wages, and to do that I need to distinguish between day rate hours and night rate hours. Basically 6 am - 10 pm is classed as day rate while 10 pm - 6 am is night rate. I've been attempting to use an IF statement to work it out, but it's ended up too complicated for me.

    Rather than explain what I've done so far, could somebody please look at where I'm up to and advise me on the best way forward? Any suggestions for improvement are also welcome. Thank you in anticipation.

    Timesheet_Help.xlsx
    Last edited by Hahnium; 02-20-2013 at 12:50 AM. Reason: To correct failed attachment

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Calculating Day Rate and Night Rate Hours on a Timesheet

    Night rate =IF(E13<H9,H9-E13,0)+IF(F13>H10,F13-H10,0) where H9 is the day rate start time, h10 is the day rate end time, e13 is the start time, f13 is the end time.

  3. #3
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    484

    Re: Calculating Day Rate and Night Rate Hours on a Timesheet

    Hi this should do the trick for you,

    you will need to have Macros enabled to use it

    https://docs.google.com/file/d/0B7vb...it?usp=sharing


    or


    http://www.4shared.com/office/hGuB1e...t_Example.html
    Last edited by Toonies; 02-19-2013 at 07:13 AM. Reason: 2nd link

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

    Re: Calculating Day Rate and Night Rate Hours on a Timesheet

    Thanks to both of you for taking the time to reply.

    nathansav, the formula you provided for night rate hours works when the shift starts during day rate and ends during night rate on the same day. This is the case for most, but not all, of my shifts. It doesn't work at all though for shifts that start on one day and finish on the next - one of my shifts started at 23:55 and finished at 06:16.... instead of returning a value of 6:05 for night hours, the formula reports 0:00. It also tells me that on the days I've not entered any data, because I haven't worked at all, that I've done 6 night hours, although to be fair, that was easily corrected. And I still haven't finished working out all the IF combinations to calculate the day rate hours yet either.....

    Toonies, wow that's an impressive timesheet! I can only hope that one day I'll have the skills to be able to understand it! As it is I've only ever used Excel for simple calculations and so I'm trying to teach myself more, and I'm sorry but I have absolutely no idea how to follow what you've done Thanks anyway.

    It seems as though my attachment failed miserably. Sorry about that, don't know what I've done wrong there either.

  5. #5
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    564

    Re: Calculating Day Rate and Night Rate Hours on a Timesheet

    Hi, Hahnium..

    I can't see your attachment, but before this has thread that may be same with you.

    http://www.excelforum.com/excel-form...53#post3126053


    Regards,
    SDCh
    Last edited by SDCh; 02-19-2013 at 10:37 PM.

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

    Re: Calculating Day Rate and Night Rate Hours on a Timesheet

    Thanks SDCh, I can just about follow how your formula works in the other case but I haven't got a clue how to adapt it to my timesheet. I think maybe I need to take a course!

    Thanks anyway

  7. #7
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    484

    Re: Calculating Day Rate and Night Rate Hours on a Timesheet

    Hi if you PM me I will give you more detail of how it works

  8. #8
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Calculating Day Rate and Night Rate Hours on a Timesheet

    Hi Hahnium

    See the attached file. Would this be of any help!
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1