0

Trying to work out a slightly more complex overtime worksheet here:

Employee gets paid:

  • 1x hourly rate during weekdays (Mon-Fri) during 08:30 to 17:30
  • 1.5x hourly rate during weekdays (Mon-Fri) before 08:30 or after 17:30
  • 1.5x hourly rate on Saturdays
  • 2x hourly rate on Sundays and Public Holidays

Sheet Layout:

|Date   |Day    |Slip No    |Name   |Destination    |Start Time |End Time   |Total Hours Worked |Basic Hours    |OT @ 1.5   |OT @ 2.0

So I'm looking for formula's for columns

  • [H] = Hours Worked
  • [I] = Basic Hours
  • [J] = OT @ 1.5
  • [K] = OT @ 2.0

I'm only looking for a hh:mm representation of what was worked; so no need for a 'hourly rate' field.

[H] =MOD(G6-F6,1)
[I] =IF(F6<G6,MIN(G6,Data!F2)-MAX(F6,Data!E2),MAX(0,Data!F2-F6)+MAX(0,G6-Data!E2))
[J] =H6-I6
[K] =IF(OR(WEEKDAY($A6)=1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=FALSE),$H6,0)

[H] = working;

[K] = working;

[I]/[J] are the issue and I feel if I could just get I working, I will have the solution for J as well. J is just "total hours" minus "basic hours".

tblPublicHolidays is a list of public holidays we have.

Data!E2 = start time - ie: 08:30

Data!F2 = end time - ie: 17:30

For the purpose of this demo, this first row (6) has a value of a start time as 18:30 and end time as 18:45. I got the [I] formula from https://exceljet.net/formula/total-hours-that-fall-between-two-times but it doesn't seem to be working for these reasons:

  • [I] shows up as a bunch of hashes (#) if I have the format as 'Time'
  • If I change [I] to Number format, then it comes up as -0.04
  • [J] seems to always have 1 hour more than its supposed to (in this example 1:15 instead of 0:15)
Improve this question
5
  • You can get #s if the content is too wide to display in the column width. What do you see if you adjust the column width?
    – fixer1234
    Commented Oct 8, 2018 at 21:56
  • Sorry I didn’t mention it; I did resizing the column - does nothing. Only changing the format to “number” yields the -0.04 I mentioned.
    – SupaMonkey
    Commented Oct 9, 2018 at 5:46
  • Should it be possible to get a negative time in [I] (perhaps related to what's giving you the 1 hr error in [J])? -.04 is equivalent to roughly - 1 hr (i.e., 0.04 of a day). If you should not be producing a result with negative time, that may be a clue. If a negative time is allowable, the reason you're getting #s is likely that you're working in Windows and using the "1900 basis" for date/time, which isn't capable of handling negative times. The Mac version of Excel defaults to the "1904 basis", which can handle negative times, and you can select that in the Windows version.
    – fixer1234
    Commented Oct 9, 2018 at 6:31
  • So: Data!E2 = 8:30 & Data!F2 = 17:30 link MIN(G6,upper) = 08:30 in the first instance MAX(F6,lower) = 18:30 in the first instance The last two examples where the 'start time'/'collect time' is before the 'lower' - it works fine The first two examples where the 'start time'/'collect time' is after the 'upper' - it doesnt work.
    – SupaMonkey
    Commented Oct 9, 2018 at 6:53
  • Sounds very fimilar to this .. Please consider adding sample data (you have pasted your formulas, which is good.. but there is no way for others to test it..)
    – p._phidot_
    Commented Oct 15, 2018 at 19:58

1 Answer 1

Reset to default
0

Thanks for the help, but this is what I ended up myself (assume starting row is 6):

A6 (DATE)
B6 (DAY) =IF(A6<>"",TEXT(A6,"dddd"),"")
F6 (START TIME)
I6 (END TIME)
L6 (HOURS WORKED) =IF(AND(F6>0,I6>0),MOD(I6-F6,1)*24,"")
M6 (BASIC HOURS) =IF(AND(F6>0,I6>0),IF(AND(WEEKDAY($A6)>1,WEEKDAY($A6)<7),IF(F6 < I6,MAX(0,MIN(I6,upper)-MAX(F6,lower))*24,MAX(0,upper - F6)+MAX(0,I6 - lower)*24),0),"")
N6 (Overtime @ 1.5) =IF(AND($F6>0,$I6>0),IF(AND(WEEKDAY($A6)>1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=TRUE),(L6-M6),0),"")
O6 (Overtime @ 2.0) =IF(AND($F6>0,$I6>0),IF(OR(WEEKDAY($A6)=1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=FALSE),$L6,0),"")
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .