day shift night shift rates of pay

[nalyom9]

Hello all,
I am new here, I have got a formula that can separate day shift hours from night hours, in this case night begins at 7pm to 7am, however the problem is after 12am we get into negative numbers, what formula would fix this and can be combined with the formulas below?


E10 = 19:00 or start of night time hours
B3 = start time
C3 = end time
D3 = day hours workeds =24*IF(E10<B3, 0, IF(C3<E10, C3-B3, E10-B3))
D4= night hours worked =24*IF(C3<E10, 0, IF(E10<B3, C3-B3, C3-E10))
These formulas make it easy for me to add on the extra pay after 7pm as there is a day hour cell and a night hour cell

Also, my other problem is there is another shift called CRO (Puts me in another department) which is paid at a diff rate, (and after 7pm it will increase as the normal shifts do) but it does not fall on any particular day and can be any where from 7am to 11pm, so in my weekly roster sometimes one or two shifts will be CRO shifts. What can I do to be able to use the formulas above and switch to the diff(CRO) rate. This shift often will pass 7PM into the night hours but won't ever go past midnight.

I have attached an example sheet

Many Thanks in advance

[shyamhappy]

Attachment 255782
Attached file will give you the solution of the number of hours worked. Here i have not combined the day and night and have just put Total Worked Hours.

Below is the formula I have used
=24*IF(AND(B3 >=$D$10,C3<=$E$10), C3-B3, IF(AND(B3<=$E$10,C3>=$E$10),C3-B3,IF(AND(B3>=$E$10,C3<=$D$10),$C$10-B3+C3,)))

As per basic rules is 7:00 to 19:00, I have calculated the working hours.

Please have a check let me know if you need any amendments or changes.

[nalyom9]

Hello,
Many thanks for your work and quick reply, however my problem was entering a shift from 7pm to 7am and getting a negative value, I have tried this in your attachment and the problem still remains. (hours worked -12) I know it has something to do with a MOD function but don't know how to incorporate it into the existing formula.
Again many thanks for you kind efforts

[oeldere]

See for that job, the link below.

http://mohammedkb.wordpress.com/2012...een-two-dates/

[oeldere]

And here you find some mor links

Excel Magic Trick 718: Calculate Hours Worked (Day or Night Shift) & Subtract Lunch

Excel Magic Trick 718: Calculate Hours Worked (Day or Night Shift) & Subtract Lunch - YouTube

Excel Magic Trick 624: Hours Worked Part Day Shift & Part For Night Shift MEDIAN Function for Hurdle
Excel Magic Trick 624: Hours Worked Part Day Shift & Part For Night Shift MEDIAN Function for Hurdle - YouTube

[jhren]

See if attached works for you....

[Teethless mama]

Those videos oeldere recommend won't work with your application.

Try these two formula:

Day Shift:
=IF(COUNT(B3:C3)=2,MEDIAN((B3>C3),C3,E$10)-MIN(B3,E$10),0)*24

Night Shift:
=IF(COUNT(B3:C3)=2,MOD(C3-B3,1)*24-J3,0)

See the attached for more detail

[Teethless mama]

See if attached works for you....

Your formula fail the following:

If Start: 7:00, end: 10:00 your formula returns -9 for night. It should be 0

If Start: 20:00, end 23:00 your formula returns -1 for day. It should be 0.

[nalyom9]

hello,
thanks for your attachment and in your sheet it is calculating the night shift correctly, however when I look at the formula in the cell eg DAY cell it looks like this in my excel program:
=IF(OR(NOT(ISNUMBER(B3)),NOT(ISNUMBER(C3))),0,(MIN(IF($C3<=$B3,$C3+1,$C3)-7/24,0.5)-($B3-7/24))*24)
and the NIGHT Cell looks like this:
=IF(OR(NOT(ISNUMBER(B3)),NOT(ISNUMBER(C3))),0,(MIN(IF($C3<=$B3,$C3+1,$C3)-19/24,0.5)-MAX(0,($B3-19/24)))*24)
I wonder is my excel 2007 an older version than yours maybe?

Thanks again

[jhren]

Your formula fail the following:

If Start: 7:00, end: 10:00 your formula returns -9 for night. It should be 0

If Start: 20:00, end 23:00 your formula returns -1 for day. It should be 0.

I see that... but your formulas aren't working correctly either!!!

For example, enter 6AM to 8PM (one hour before day shift, one hour after), and your formula returns 13 for day shift and 1 for night shift. While the total is correct, you can't have any more than 12 hours per shift.

I redid my file and replaced it...

http://www.excelforum.com/attachment...9&d=1375820987

[jhren]

@nalyom9

Disregard that file.

I replaced it...

http://www.excelforum.com/attachment...9&d=1375820987

I'm pretty certain this covers all possibilities (fingers crossed while making that statement )...

Spoke too soon. Already found same error I pointed out about teethles', only 13 in night shift, 1 in days...

[jhren]

Latest revision...

http://www.excelforum.com/attachment...9&d=1375823954

Please test extensively!!!

[nalyom9]

Hello,
Hey nice job Teethless Mama and thanks! your formula does work! I have now set the total hours column to simply add both day and night columns. The only small problem I still have to solve is the CRO shift (Puts me in another department) which is paid at a diff rate, (and after 7pm it will increase as the normal shifts do) but it does not fall on any particular day and can be any where from 7am to 11pm, so in my weekly roster sometimes one or two shifts will be CRO shifts. What can I do to be able to use the formulas above and switch to the diff(CRO) rate. This shift often will pass 7PM into the night hours but won't ever go past midnight.

Again thanks so much for nailing the formula works a treat!! And thanks to everyone else for trying to help!

John

[jhren]

@nalyom9

Considering other methods, but in doing that, I have a question...

If a worker starts at or after midnight, is it reported as starting the prior day or new day. For example, John comes in late for his Monday night shift and starts at 1AM... is this recorded as Monday work or Tuesday.

I guess that prompts another question, because I work the same night shift at times... is the night shift reported as the day it starts on or the day it ends on... because I have worked places were it has been either.

[nalyom9]

Hello,
Well in my work night shift is 7pm to 7am and is the day you clock in, some other shifts may start in the morning or afternoon and run past 7pm but not past 12am. Also as we get time and a half for Sunday it only counts if the night shift starts on a Sunday. In other words if night shift starts on Saturday 7pm no time and a half is paid for working into Sunday morning. The flip side is of course Sunday night shift running into Monday morning is all paid at time and a half

[jhren]

If you want more on how to do the CRO thing, you will have to provide more info: how it is scheduled, how times are reported and differentiated, and how the differentiated times are paid....

[Teethless mama]

Latest revision...

http://www.excelforum.com/attachment...9&d=1375823954

Please test extensively!!!

Your formula fail badly...

Start: 22:00, End: 23:00 your formula returns 12 hrs for day, and -11 hrs for night. It should be 1 hr for night.

start 23:00 end 2:00 result 12hrs for day, -9 for night. It should be 3hrs for night.

[Teethless mama]

Hello,
Hey nice job Teethless Mama and thanks! your formula does work! I have now set the total hours column to simply add both day and night columns. The only small problem I still have to solve is the CRO shift (Puts me in another department) which is paid at a diff rate, (and after 7pm it will increase as the normal shifts do) but it does not fall on any particular day and can be any where from 7am to 11pm, so in my weekly roster sometimes one or two shifts will be CRO shifts. What can I do to be able to use the formulas above and switch to the diff(CRO) rate. This shift often will pass 7PM into the night hours but won't ever go past midnight.

Again thanks so much for nailing the formula works a treat!! And thanks to everyone else for trying to help!

John

You're Welcome!

[nalyom9]

If you want more on how to do the CRO thing, you will have to provide more info: how it is scheduled, how times are reported and differentiated, and how the differentiated times are paid....

Hello,
G5 =10.88*D5+E5*2 10:88 is basic rate of pay, CRO rate is 11.33, so a CRO shift can be given on any day of the week for an entire shift, I thought maybe if I had a column alongside each day (H column maybe) that was simply a value either 0 or 1 then if a particular day was a CRO shift ie paid at slightly higher rate of 11.33 could you say If H5=1 then G5=11.33*D5+E5*2. I know thats not really how the formula would look but maybe you can follow what I mean? It's essentially a true or false thing like the over time =IF(F10>39,(F10-39)) in other words if total hours for the week exceed 39 then pay at time and a half.

[jhren]

Hello, nalyom9

See attached file...

I added VBA-controlled checkboxes (actually just two Wingdings characters) that change state on selection. Doesn't matter how they are selected... a caveat of the macro. I'm still a VBA noob, so perhaps someone knows how to modify it so it only functions on mouse clicks...???

Also, I tried to adapt Teethless' formula to my version as best I could... but I'm still skeptical of results. Note Thursday's shift hours when you open the file...

caio!

[nalyom9]

hello,
Thanks, The attachment won't open, however I think I have solution however I dont know how to attach a file here, anyway, the money column looks like this =IF(H3=1,11.33*F3+E3*2,10.88*F3+E3*2)
So if H3 is 0 normal rate of pay applies if I change h3 to 1 if the shift is CRO the higher rate applies. so across from any day that was CRO rate I simply type 1 in the H column and it ups the rate of pay

[nalyom9]

hello,
Thanks, The attachment won't open, however I think I have solution however I dont know how to attach a file here, anyway, the money column looks like this =IF(H3=1,11.33*F3+E3*2,10.88*F3+E3*2)
So if H3 is 0 normal rate of pay applies if I change h3 to 1 if the shift is CRO the higher rate applies. so across from any day that was CRO rate I simply type 1 in the H column and it ups the rate of pay

[jhren]

Don't know why you can't open the attachment. I'm not fully aware of the differences between 2010 and 2007 versions. Perhaps someone else can advise.

My file essentially does the same as far as using column H to change rates.. just that checkboxes look better than 1's and 0's

Your formula is nearly identical to mine...

=IF(H3="þ",11.33,10.88)*F3+E3*2

The "þ" character is the equivalent of a checked checkbox in Wingdings font.

[nalyom9]

Oh I see! and how do you get that symbol, what keys do you use?

[jhren]

For a Wingdings checked checkbox, ALT+0254. (You can lookup character codes in Ribbon|Insert|Symbol's dialog. For 3-digit codes you have to use 4 digits with leading zero (0). If you manage to get my file open, the VBA code inserts the checked or unchecked boxes simply by selecting the cell.

Also, I finally figured out a formula which has no time errors. Thoroughly tested, too!

=MAX(0,(MIN([shift_end],IF([work_end]<[work_start],[work_end]+1,[work_end]))-[work_start]-IF([work_start]<=[shift_start],[shift_start]-[work_start]))*24)

All references from Time formatted cells or time serial values (e.g. 7/24 is 7AM).

[nalyom9]

great job! Ill try it out thanks so much!

[jhren]

As far as the formula.... I hate to say it, but it fails on certain combinations of start/end times, namely where both are inside normal shift hours but include the full other shift (e.g. start on day shift, work through night shift and into following day shift). Working to fix that...

[jhren]

Okay, revised formula which truly works...!!!

D3: =IF(COUNT(B3:C3)=2,(MAX(0,(MIN(19/24,IF(B3>C3,C3+1,C3))-MAX(7/24,B3)))+IF(AND(B3>C3,C3>7/24,C3<19/24),C3-7/24))*24,0)

E3: =F4-D4

F3: =IF(COUNT(B3:C3)=2,(IF(C3<=B3,C3+1,C3)-B3)*24,0)


I realize there is probably minimum chance the errors of a basic formula would pop up... but you likely do not want to deal with them when they do.

[nalyom9]

I tried your formula but doesn't seem to work, the previous one does, except on my kindle fire HD where I use documents to go program, it underlines the word MEDIAN in the formula and I guess only excel 2007 and after can understand the MEDIAN function, where as Docs to go aint as advanced perhaps.

In my roster at least the only shift we do going into AM is 7pm-7am all other shifts can only be between 7am to 11pm anywhere from 4 to a max of 12 hours, so I may not come across the errors you spoke of.
Also I made the þ thing and put it in the formula but it just looks like, well this þ I dont know how one would tick the cell and I have a few wingding fonts installed.

[jhren]

well done! In my roster at least the only shift we do going into AM is 7pm-7am all other shifts can only be between 7am to 11pm anywhere from 4 to a max of 12 hours, so I may not come across the errors you spoke of.

I thought so... but just in case

Also, there are others out there that may need to handle the errors described.

Also I made the þ thing and put it in the formula but it just looks like, well this þ I dont know how one would tick the cell and I have a few wingding fonts installed.

The cell's font has to be set to Wingdings to display as a checkbox.

Much easier with VBA... than typing character codes.

Please Login or Register  to view this content.

The above code is modified for your sheet and rather than just selecting the cell, I changed it so you actually have to enter a space character to get the checked box. Any other entry, including select cell and delete contents, returns an empty checkbox. You may want to add a comment to the cells or the header to this effect.

To add the code to your workbook, press ALT+F11. Should open vba editor. In the explorer-type navigation on left, double click your sheet name. A code window will open to right. Simply copy code above and paste in that window, then close the editor. Save As filetype .xlsm.

[nalyom9]

Wow! great stuff thanks so much for all that, I did get it to work via character map and added it to the recently used section, I will try your code too.

Thanks again

[jhren]

You're Welcome...!!!

If you (or co-worker) are going to be doing this regularly, which appears to be weekly per employee, the Code method is the way to go.


On another issue, you may need to adjust your calculation method of overtime pay. The formula you have in your example file calculates based on hours worked over 39 and 1.5 times the regular 10.88 rate. Wouldn't you have to calculate 1.5 times the CRO rate if the 39th hour transition occurs during or before a CRO shift?

[nalyom9]

Hello,
CRO is just a relief position which I get about 3 or 4 times a month. As regards over time, it makes no difference what shift I'm on at the time it passes 39th hour, I guess full time cro staff get time and a half after 39 hours but * cro rate. So the OT calculation should be ok.

[jhren]

Okay... didn't know so I thought I'd mention it

caio

[nalyom9]

Cool much obliged