Calculating Shift Differentials within Microsoft Excel - Formula Needed

[TheyCallMeA7]

Hey Everyone,

I really hope someone can help me. This is probably a relatively simple solution; however, I've been at this all night and my brain is fried and am having issues trying to come up with a formula.

So simply put, I've noticed a few pay discrepancies within my paycheck over the past couple of months and wanted to design a excel sheet to calculate my hours and rough expected pay - taxes being what they are.

My employer offers shift differentials which is throwing a wrench in my process. Can someone please help me come up with a formula for this.

My excel sheet is setup in 24hr format.

What I am going to need for formulas is for the following

Evening Differential -1500hrs - 2259hrs

Night Differential - 2300hrs and 0659hrs

Weekend Differential - Friday 1900hrs - Monday 0659hrs

If someone could create a formula that can detect the hours mentioned and calculate a total of hours worked, I can then take it from there.

Attached is a sample image of how I am setting up my spreadsheet.

I appreciate the assistance in advance.

 

[aek_nikos]

=IF(ISBLANK(C3);0;IF(C3>B3;C3-B3;IF(B3>C3;24-(B3-C3);"!!!")))

Try this and tell me

 

[TheyCallMeA7]

=IF(ISBLANK(C3);0;IF(C3>B3;C3-B3;IF(B3>C3;24-(B3-C3);"!!!")))

Try this and tell me

You're definitely onto something here.. I've managed to reorganize the worksheet out a little bit to better assist with the equations.

I've recreated the excel sheet to calculate hours being worked daily and then Talley them up in another cell weekly and then biweekly, and in another cell calculate the amount of money earned from that time.

With your sample equation above I was able to come up with the following myself, that works, only kind of...

=IF(E12>=M60,E12-M60,E12*0)

This Equation works only if the person takes their lunch before 1500hrs. And don't get me started on the night shift one.

And then further down the road it's creating mathematical issues, as for whatever reason... it's pulling as a decimal, "0.17" when I convert the decimal to time formatting it displays 4:00 which is right but doesn't work correctly adding up the paycheck within the calculator.

I swear ADHD is a blessing and a curse...

Again, I've been slamming this spreadsheet out all night.

Literally have completed everything, and more. The spreadsheet even calculates biweekly accrued PTO time based off hire date.

I just can't grasp why I can't put this idea into an equation.

 

[TheyCallMeA7]

You're definitely onto something here.. I've managed to reorganize the worksheet out a little bit to better assist with the equations.

I've recreated the excel sheet to calculate hours being worked daily and then Talley them up in another cell weekly and then biweekly, and in another cell calculate the amount of money earned from that time.

With your sample equation above I was able to come up with the following myself, that works, only kind of...

=IF(E12>=M60,E12-M60,E12*0)

This Equation works only if the person takes their lunch before 1500hrs. And don't get me started on the night shift one.

And then further down the road it's creating mathematical issues, as for whatever reason... it's pulling as a decimal, "0.17" when I convert the decimal to time formatting it displays 4:00 which is right but doesn't work correctly adding up the paycheck within the calculator.

I swear ADHD is a blessing and a curse...

Again, I've been slamming this spreadsheet out all night.

Literally have completed everything, and more. The spreadsheet even calculates biweekly accrued PTO time based off hire date.

I just can't grasp why I can't put this idea into an equation.

Resolved the decimal problem, Some of my cells were formatted wrong, was half the issue. The other half was I made a stupid mistake and forgot a key part of the equation. It's been forever since I've played around with Excel...

Current Equation I have is as follows:

=IF(E12>=M60,E12-M60,E12*0)*24

This is pulling in numerical format, however, is only calculating based off punch out time. It's not taking into account any lunch breaks that may or may not be taken after 1500hrs, nor is it setup for night shift either...

 

[TheyCallMeA7]

You're definitely onto something here.. I've managed to reorganize the worksheet out a little bit to better assist with the equations.

I've recreated the excel sheet to calculate hours being worked daily and then Talley them up in another cell weekly and then biweekly, and in another cell calculate the amount of money earned from that time.

With your sample equation above I was able to come up with the following myself, that works, only kind of...

=IF(E12>=M60,E12-M60,E12*0)

This Equation works only if the person takes their lunch before 1500hrs. And don't get me started on the night shift one.

And then further down the road it's creating mathematical issues, as for whatever reason... it's pulling as a decimal, "0.17" when I convert the decimal to time formatting it displays 4:00 which is right but doesn't work correctly adding up the paycheck within the calculator.

I swear ADHD is a blessing and a curse...

Again, I've been slamming this spreadsheet out all night.

Literally have completed everything, and more. The spreadsheet even calculates biweekly accrued PTO time based off hire date.

I just can't grasp why I can't put this idea into an equation.

I seem to have the solution in place, and it accounts for lunch times, as well as clocking out early.

=IF(D22>=M60,E22-M60-(D22-M60),(E22-M60))*24

This solution simply add how may hours were worked for a certain shift differential. From there in a separate field you can calculate the hours worked with the amount offered and get how much money you make in differential pay.

 

[TheyCallMeA7]

So those of you that need this formula. After further trial and error I found issues with the previously posted formula. I've further perfected the formula and I believe this one is correct, It accounts for partial lunch punches as the other did not.

=IFS(AND(E23>=N72,D23<N72,C23<N72),E23-N72,AND(E23>=N72,D23>=N72,C23<N72),E23-N72-(D23-N72), AND(E23>=N72,D23>=N72,C23>=N72),E23-N72-(D23-C23))*24

I seem to have the solution in place, and it accounts for lunch times, as well as clocking out early.

=IF(D22>=M60,E22-M60-(D22-M60),(E22-M60))*24

This solution simply add how may hours were worked for a certain shift differential. From there in a separate field you can calculate the hours worked with the amount offered and get how much money you make in differential pay.

I'm not sure why it was working but the previous formula no longer worked after posting. That being said it also didn't account for things such as staying late, and showing late. So i've tweaked the code and tested it thoroughly, this one seems to have resolved any and all issues for those that need it in the future.

=IFS(AND(E12<=Q72,C12<=N72,D12<N72,B12<=N72),E12-N72,AND(E12<=Q72,C12<=N72,D12>=N72,B12<=N72),E12-N72-(D12-N72),AND(E12<=Q72,C12>=N72,D12>N72,B12<=N72),E12-N72-(D12-C12),AND(B12>=N72,C12>=N72,D12>N72,E12<=Q72),E12-B12-(D12-C12),AND(B12<=N72,C12>=N72,D12>N72,E12>Q72),Q72-N72-(D12-C12),AND(B12<=N72,C12<N72,D12>=N72,E12>Q72),Q72-N72-(D12-N72),AND(B12<=N72,C12<N72,D12<N72,E12>Q72),(Q72-N72))*24

Now all that being said, I don't believe this formula will work with overnight shifts, it's only for employees scheduled between the times of 00:00 - 23:59hrs.