Hours Worked in Time Period

[Stefan Teuthof]

Hi,

Firstly love this website, its so helpful, thanks to everyone who puts in the time to help others.

I am desperately trying to work out a formula that calculates the number of hours worked in certain time periods on certain days of the week so I can workout pay rates.

I have found a couple of formulas that sort of work but seem to have limitations with 24 hr time. I have spent hours trying to work it out but I just cant get it.

The attached workbook provides a little more info, I have included what I have done in the workbook just as an example. In the example I tried to use two different formulas to cover night and day as I couldn't get it to work, but I haven't even considered days worked either yet.

So I am trying to include a few things (The workbook probably explains it better)

1. Hours worked in the time period (Night and Day)
2. First 8 hours (not including lunch) hours worked in that time period.
3. Dependent on days worked as weekends and public holidays have different rates.
(If public holidays are too hard to include don't worry about that part)

Hoping this might be easy for someone, but its certainly not for me unfortunately

Any help would be very much appreciated

Thanks in advance.
Stefan

 

[AVK]

Suppose manually enter date in "A2"
In "B2" =INDEX(B$8:B$16,MATCH($A$2,$A$8:$A$16,0))
In "C2" =INDEX(C$8:C$16,MATCH($A$2,$A$8:$A$16,0))
In "D2" =INDEX(D$8:D$16,MATCH($A$2,$A$8:$A$16,0))
In "E2" =INDEX(E$8:E$16,MATCH($A$2,$A$8:$A$16,0))
In "F2" =INDEX(F$8:F$16,MATCH($A$2,$A$8:$A$16,0))
In "G2" =INDEX(G$8:G$16,MATCH($A$2,$A$8:$A$16,0))

 

[Stefan Teuthof]

Suppose manually enter date in "A2"
In "B2" =INDEX(B$8:B$16,MATCH($A$2,$A$8:$A$16,0))
In "C2" =INDEX(C$8:C$16,MATCH($A$2,$A$8:$A$16,0))
In "D2" =INDEX(D$8:D$16,MATCH($A$2,$A$8:$A$16,0))
In "E2" =INDEX(E$8:E$16,MATCH($A$2,$A$8:$A$16,0))
In "F2" =INDEX(F$8:F$16,MATCH($A$2,$A$8:$A$16,0))
In "G2" =INDEX(G$8:G$16,MATCH($A$2,$A$8:$A$16,0))

Thanks for your reply, I really appreciate it.

I tried doing what you said just to make sure Im not missing something. But maybe I didn't explain clearly or Im not understanding.

I am trying to work out the hours per shift, e.g. if someone works 07:00 till 19:00 on a Monday without a lunch break, they would get 8 hrs of "Mon-Fri first 8 hours (0600-1800)" and 3 hrs of "Mon-Fri > 8 hours (0600-1800)" and 1 hr of "Out of hours Mon-Thurs >8 hours (1800-0600)" which makes a total of 12 hrs. If they did have a lunch break it wouldn't be included.

 

[Chihiro]

I believe you have mistake in your result (Row 10, should have 1 hour in Out of Hours).

Also I'm unsure on how you want lunch hours treated.

Without considering Lunch/Break. Formula would be...
I8:
=IF(((C8>F8)*MEDIAN(0,F8-6/24,12/24)+MAX(0,MIN(18/24,F8+(C8>F8))-MAX(6/24,C8)))*24>8,8,((C8>F8)*MEDIAN(0,F8-6/24,12/24)+MAX(0,MIN(18/24,F8+(C8>F8))-MAX(6/24,C8)))*24)*(MEDIAN(WEEKDAY(A8,2),1,5)=WEEKDAY(A8,2))

J8:
=IF(((C8>F8)*MEDIAN(0,F8-6/24,12/24)+MAX(0,MIN(18/24,F8+(C8>F8))-MAX(6/24,C8)))*24>8,((C8>F8)*MEDIAN(0,F8-6/24,12/24)+MAX(0,MIN(18/24,F8+(C8>F8))-MAX(6/24,C8)))*24-8,0)*(MEDIAN(WEEKDAY(A8,2),1,5)=WEEKDAY(A8,2))

K8:
=IF(((C8>F8)*6/24+MIN(F8,6/24)-MIN(C8,6/24)+(C8>F8)*6/24+MEDIAN(F8,18/24,1)-MEDIAN(C8,18/24,1))*24>8,8,((C8>F8)*6/24+MIN(F8,6/24)-MIN(C8,6/24)+(C8>F8)*6/24+MEDIAN(F8,18/24,1)-MEDIAN(C8,18/24,1))*24)*(MEDIAN(WEEKDAY(A8,2),2,4)=WEEKDAY(A8,2))

L8:
=IF(((C8>F8)*6/24+MIN(F8,6/24)-MIN(C8,6/24)+(C8>F8)*6/24+MEDIAN(F8,18/24,1)-MEDIAN(C8,18/24,1))*24>8,((C8>F8)*6/24+MIN(F8,6/24)-MIN(C8,6/24)+(C8>F8)*6/24+MEDIAN(F8,18/24,1)-MEDIAN(C8,18/24,1))*24-8,0)*(MEDIAN(WEEKDAY(A8,2),2,4)=WEEKDAY(A8,2))

M8:
=ROUND(((F8-C8+(F8<C8))*24-SUM(I8:L8)),2)

See attached.

Note: Haven't tested all scenario. Test and let me know if you find discrepancy(s).

 

[Stefan Teuthof]

That's absolutely amazing thanks so so much

Lunch breaks (if taken) are always taken within the first 8 hrs of a shift, so if there is some where to minus the lunch break then perform the calculation that would be brilliant.

Thanks again I really appreciate this very much, Im very excited to use it and save heaps of time.

 

[Chihiro]

Did a quick test. Current formula will cause issue when shift starts on Monday and when there is work hour that falls between Mon 1800 - Tue 0600.

I'll look at this tomorrow and will also add lunch hour calculation.

 

[Stefan Teuthof]

I just tried putting a shift on a Mon from 18:00 till 06:00 and all the hours went into weekends. So if you mind looking at that one for me that would be great. I tried a Tuesday with same times and it worked perfectly.

Did a quick test. Current formula will cause issue when shift starts on Monday and when there is work hour that falls between Mon 1800 - Tue 0600.

I'll look at this tomorrow and will also add lunch hour calculation.

Thanks

 

[p45cal]

5 new formulae in large pale green area.
Day of week corrected in column B.
Note that for clarity, I've opted to show zeroes as blanks in Excel's Options.
It's an adaptation of my offering here:
http://www.excelguru.ca/forums/show...rentiate-betwen-Ordinary-Night-SAT-SUN-shifts

 

[Stefan Teuthof]

Its so so close, I have attached some examples of where it hasn't calculated correctly. Seems like an issue with lunch in some of these cases. I am so impressed with your skills, I don't think I could ever get to that level.

 

[p45cal]

Right, I'll rewrite the functions so that they'll be a simpler formula. They'll be like:
=StandardHours($A2,$C2:$F2).
One question: They could be even simpler:
=StandardHours($A2:$E2)
were it not for the intervening column B.
Do we really need this column? No formulae seem to use it. The day of the week information could be shown by a format change to column A so that the dates therein appear like:
Sat 11 June 2016
or:
11 June 2016 (Sat)
or some such.

 

[Stefan Teuthof]

Thanks so much, yes please feel free to delete column B it was just something I added when I was trying to work a solution. But yes you are correct its not needed. Thanks heaps

 

[Chihiro]

See attached. Simplified formula a bit and added lunch time calculation.

One question. Will there be any case where start time and end time will span 3 shift range or more? If so, I'd go with UDF method instead of formula alone (it will add another layer of complexity to formula).

Ex:
Starting @ Monday, Oct 10, 2016, 05:30
Ending @ Monday Oct 10, 2016, 20:00

In above case hours will span...
Mon-Fri (0600-1800) - 12 hours
Out of Hours Mon-Thu (1800-1600) - 2 hours
Weekend - 0.5 hours

 

[p45cal]

See attached.
Originally the results were to be in days so that a date/time format could be applied.On sheet Example(2) columns I:K there are some examples of such, as well as some deliberate errors in the first 10 or so rows. These errors don't show on sheet Example because they're calculated upon (eg. multiplied by 24 to get hours).
You have someone working very long shifts on row 23!

 

[Stefan Teuthof]

See attached. Simplified formula a bit and added lunch time calculation.

One question. Will there be any case where start time and end time will span 3 shift range or more? If so, I'd go with UDF method instead of formula alone (it will add another layer of complexity to formula).

Ex:
Starting @ Monday, Oct 10, 2016, 05:30
Ending @ Monday Oct 10, 2016, 20:00

In above case hours will span...
Mon-Fri (0600-1800) - 12 hours
Out of Hours Mon-Thu (1800-1600) - 2 hours
Weekend - 0.5 hours

Thanks for this. Yeah they could cross over into three categories unfortunately.
Thanks

 

[Stefan Teuthof]

See attached.
Originally the results were to be in days so that a date/time format could be applied.On sheet Example(2) columns I:K there are some examples of such, as well as some deliberate errors in the first 10 or so rows. These errors don't show on sheet Example because they're calculated upon (eg. multiplied by 24 to get hours).
You have someone working very long shifts on row 23!

Thanks very much I will review and test as soon as I can, but looks like it working. I will also check my data source because it looks like row 23 had a lunch break outside of the hours worked which is why the hours were so big. They did a night shift but had lunch during the day. Administration error by the looks.

I love the error prompts, they are a great addition.

 

[Stefan Teuthof]

I have just been informed that the way my boss explained how this award was interpreted was wrong. The payroll lady is going to send me an email with the correct way. I am so sorry I now have learned that you should always ask the person who actually does the job not the boss. I will come back with the adjustment once I have been advised in writing. I am very sorry but the good news is I can still use these for another clients billing.

 

[Stefan Teuthof]

I just had another idea to avoid this again, what if the rules were flexible and you choose the rule from a drop down. (Example excel attached) There is now a tab called "rules", then in the tab called "example" the top headers could now be drop downs where you pick the rule.
I totally understand if this is too complicated and time consuming, but thought this could then be a template to accommodate any award variations for anyone.

 

[p45cal]

I just had another idea to avoid this again, what if the rules were flexible and you choose the rule from a drop down.<snip> top headers could now be drop downs where you pick the rule.
I totally understand if this is too complicated and time consuming, but thought this could then be a template to accommodate any award variations for anyone.

You are right, such a thing would require significant work, especially as checks and balances woiuld have to be put in place to check for such things a no overlapping times, no spaces between times for the combination of rules chosen by the user.

Will adjust my current offering once we get:

I will come back with the adjustment once I have been advised in writing.