Calculating Day Rate and Night Rate Hours on a Timesheet

**Hahnium** · 02-18-2013, 09:19 PM

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

**nathansav** · 02-19-2013, 06:29 AM

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.

**Toonies** · 02-19-2013, 07:06 AM

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

**Hahnium** · 02-19-2013, 10:24 PM

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.

**SDCh** · 02-19-2013, 10:34 PM

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

**Hahnium** · 02-20-2013, 12:47 AM

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