Timesheet Calculation ~ Shift Differential (Time Worked)

[jlwitten]

OK. I Give!

I've been trying to create (or copy) a formula that will calculate the amount of hours worked before 06:00 and after 18:00, and also deduct for lunch if within the aforementioned time frame. This is what I have so far, but it ain't happening
=IF(AND(MOD(D8,1)< TIME(18,0,0),MOD(D8,1)> TIME(6,0,0)), TIME(18,0,0)-MOD(D8,1),0)+IF(AND(MOD(I8,1)< TIME(18,0,0),MOD(I8,1)> TIME(6,0,0)),MOD(I8,1)-TIME(6,0,0),0)

I've also attached the time sheet for your reference.
Thanks!

 

[Somendra Misra]

Hi jlwitten,

Well Your description and formula doesn't seems to give same understanding. Based on the formula, see the attached file, with formula in Yellow cells, Green cells are helper cells. I had not considered column G & H in calculation.

Edit: Also the formula will not account for any overtime i.e. after 18:00 or before 06:00.

Regards,

 

[jlwitten]

Thank you very much for your assistance...But what I am trying to do in the unnamed column is calculate the number of hours worked between 18:00 and 06:00 (in 10 minute increments) as those hours are shift pay. For example if the employee starts at 05:00 and ends their shift at 18:30 then they are entitled to 2:30 shift pay.

Thanks

 

[Somendra Misra]

Thank you very much for your assistance...But what I am trying to do in the unnamed column is calculate the number of hours worked between 18:00 and 06:00 (in 10 minute increments) as those hours are shift pay. For example if the employee starts at 05:00 and ends their shift at 18:30 then they are entitled to 2:30 shift pay.

Thanks

@jlwitten

The text in unnamed column has two formula in it which look similar in structure but differs in values.

Secondly, when you say "between 18:00 and 06:00" you mean night shifts as in your example of 05:00 and 18:30 it is day shift.

Third, how you came to value 2:30???

Regards,

 

[jlwitten]

The unnamed colume has two formulas because I was trying to compute the shift hours for the start time (in the case of the 1 hour before 06:00) plus the shift hours for the end time (in the case of the example 1:30 hours).

We do not have a "night shift", but there are many occassions when an employee is called on to work before 06:00, but mostly after 18:00.

The value of 2:30 is the start shift hours (1:00) plus the end shift hours (1:30)

Maybe I'm just over complicating the whole thing. What about =IF(D9<6/24,6/24-D9)+IF(I9>18/24,18/24-I9)...BUT IT ISN'T WORKING...

 

[Somendra Misra]

Can just post few cases of In/Out time with manual output.

Regards,

 

[jlwitten]

I don't understand your response. Are you suggesting that I don't use a formula at all? It would be much easier for our payrole clerk if it auto calculated.

 

[Somendra Misra]

No, I think you mis-understood my comment. I am asking that the sample file you posted was blank, I want you to post a sample file with some data in it and also put the required output in it.

Regards,

 

[jlwitten]

Sample attached...Thanks again!

 

[Somendra Misra]

Can you explain this part of your comment#5.

We do not have a "night shift", but there are many occassions when an employee is called on to work before 06:00, but mostly after 18:00

"But mostly after 18:00"...what do you mean by this????

Regards,

 

[jlwitten]

Hum...How can I explain. The film crew work after 18:00 to film public meetings, but others have need to film events before 06:00. The former is more common than the latter.

 

[Somendra Misra]

The value you posted in the file of comment #9 in column J is your expected output with the sample data.

Regards,

 

[jlwitten]

Column J is the total number of hours worked; the intention is for column K to calculate the Shift hours (before 06:00; after 18:00). It is important to note that the shift hours are included in the reg pay and then again entered in as shift pay. Second sample attached

 

[Somendra Misra]

Try this:

Put in R1: 06:00
in S1 : 18:00
than in K9 use below formula and copy down

=IF(D9<$R$1,$R$1-D9)+IF(I9>$S$1,I9-$S$1)

Regards,

 

[jlwitten]

Uh. You Rock.

Now how/where do I fit in the If=0, ''''

 

[Somendra Misra]

@jlwitten

Two ways of doing the same.

1. Put your formula like =IF(formula=0,"",formula)
2. Custom format the cells with formula to #;-#;;@

Regards,

 

[jlwitten]

Ok...I'm so sorry to be such a knucklehead, but I tried and just can't make it work...
IF((D8<6/24,6/24-D8)+IF(I8>18/24,I8-18/24)=0,"",IF(D8<6/24,6/24-D8)+IF(I8>18/24,I8-18/24))

and #;-#;;@ results in a blank cell.

And if I only enter the end of shift "out", the result is the shift time
If I only enter the start of shift "in", the result is ######
If I don't enter a day the default is 6:00

Example attached
Example attached

 

[Somendra Misra]

@jlwitten

Can you explain me why you need to only enter 11:00 in E11 and no out time. WHat information such a data will give to you. Or what you expect in such situations?

Regards,

 

[jlwitten]

The 11:00 was just a random time I entered to test the formula. I have attached a more realistic timesheet for your reference.

Thanks for all your help and sorry again for being a knucklehead...

 

[Somendra Misra]

@jlwitten

See the attached file. I had assume that Column D IN and column I Out will always be entered, if there is a chance that those cells can be blank that what output you expect.

Regards,

 

[jlwitten]

Is "I Love You" to strong of a word (well 3 words). I was soooooo close. If the D IN and column I Out are entered the time should be deducted from column J, for which the formula is correct. I've attached an example for your reference. Thanks so much for your time.

 

[Somendra Misra]

Glad you solve it. Welcome back anytime!!

Regards,

 

[mpd]

Similar problem to the above string. Our nightshift differential is paid between the hours of 5p & 7a the following morning. Anytime they work between these hours, they receive a stipend. The regular hours worked are listed in B13-H15 with corresponding NSDiff on row B31-H31. Overtime hours worked on listed in B16-H17 with NS OT Diff on row B32-H32.

Any help would be appreciated!!! Thank you!!!