Help with overtime formula for a timesheet

[leoningold]

Hi,

I am working on a timesheet for work and have had a lot of success with trial and error, but I am now completely stuck.

I am using excel 2010 and what I am trying to achieve is the following:

With the current formula that I have worked out so far - if someone works less than 7.6 hours a day, no overtime is added to the overtime cell. I am happy with that.

=SUMIF(G21:P21,">7.6")-(COUNTIF(G21:P21,">7.6")*7.6)

The current formula will not stop doing a countif and a sum if they work more than 9.6 hours a day.

What I need the formula to do is:

When someone works more than 7.6 hours a day and less than or equal to 9.6 hours a day, then the time in hours is added up for the week and the total value of overtime should end up in the time and a half cell.

When someone does more than 9.6 hours a day, then any overtime past 9.6 hours a day ends up in the double time cell for the week.

=SUMIF(G21:P21,">9.6")-(COUNTIF(G21:P21,">9.6")*9.6)

This formula works ok.

If anyone can help fix my first formula that would really make my week. I am completely stuck.

If it means a completely different type of formula, and if it works, then I am more than happy to use it.

All the best,

Leon

 

[AlphaFrog]

If you have Excel 2007 or later...

=SUMIFS(G21:P21, G21:P21,">7.6", G21:P21,"<=9.6")-(COUNTIFS(G21:P21,">7.6", G21:P21,"<=9.6")*7.6)

If you have Excel 2003 or earlier

=SUMIF(G21:P21,">7.6")-SUMIF(G21:P21,">9.6")-((COUNTIF(G21:P21,">7.6")-COUNTIF(G21:P21,">9.6"))*7.6)

 

[leoningold]

Thankyou to AlphaFrog - that is a massive help. I really appreciate you taking the time to look at my formula!

I tried the formula, and it works beautifully! You have done exactly what I asked for.

P.S:

Just one tiny request to get the formula to do exactly what I am after, of which I didn't make it clear in the first post, so sorry for that:


If overtime is worked between the hours of 7.6 and 9.6 any day of the week, then if on any given day if they go over 9.6 hours then 2 hours is automatically summed in the overtime column. At the moment with the formula from the previous post, if they go over 9.6 hours, then no hours are summed in the overtime column.


For example on Monday they may work 8.6 hours and on Tuesday they work 10.6 hours. Therefore in the overtime column 1 hour from monday is added 2 hours from Tuesday and my other formula will calculate 1 hour on Tuesday as 1 hour at double time rate.

At present, if they do 10.6 on Monday and 10.6 hours on Tuesday, then 0 appears in the overtime column because it does not meet the criteria of the formula.


I am very happy with the formula from AlphaFrog, I just need to tweak it a little - but almost there!

If AlphaFrog or anyone can assist, that would be great news.

Cheers Leon

 

[AlphaFrog]

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="width:30px; " /><col style="width:114px;" /><col style="width:64px;" /><col style="width:22px;" /><col style="width:40.67px;" /><col style="width:40.67px;" /><col style="width:40.67px;" /><col style="width:40.67px;" /><col style="width:40.67px;" /><col style="width:40.67px;" /><col style="width:40.67px;" /><col style="width:40.67px;" /><col style="width:40.67px;" /><col style="width:40.67px;" /></colgroup><tr style="background-color:#cacaca; text-align:center;font-size:8pt; "><td > </td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">Regular Hrs</td><td style="text-align:center; ">76</td><td > </td><td style="background-color:#99ccff; text-align:center; ">8.6</td><td style="text-align:center; ">7.6</td><td style="background-color:#ffff99; text-align:center; ">10.6</td><td style="text-align:center; ">7.6</td><td style="background-color:#99ccff; text-align:center; ">8.6</td><td style="background-color:#99ccff; text-align:center; ">8.6</td><td style="background-color:#ffff99; text-align:center; ">10.6</td><td style="background-color:#99ccff; text-align:center; ">8.6</td><td style="text-align:center; ">7.6</td><td style="text-align:center; ">7.6</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">Time and 1/2 Hrs</td><td style="background-color:#99ccff; text-align:center; ">4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="text-align:right; ">Double Time Hrs</td><td style="background-color:#ffff99; text-align:center; ">6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E21</td><td >=SUM(G21:P21)-E22-E23</td></tr><tr><td >E22</td><td >=SUMPRODUCT(--<span style=' color:008000; '>(G21:P21>7.6)</span>,--<span style=' color:008000; '>(G21:P21<=9.6)</span>,<span style=' color:008000; '>(G21:P21-7.6)</span>)</td></tr><tr><td >E23</td><td >=SUMPRODUCT(--<span style=' color:008000; '>(G21:P21>9.6)</span>,<span style=' color:008000; '>(G21:P21-7.6)</span>)</td></tr></table></td></tr></table> <br /><br />


SUMPRODUCT function

 

[leoningold]

Thanks again AlphaFrog,

I really appreciate that you have given me some more help! I did my best to try and adapt it and it is so close, I can smell it!

I don't mean to be a pain, but I need to just tweak it a bit as it isn't quite giving me the result i need.


The rule at work is:

if someone works up to 7.6 hours a day, then paid ordinary.

if someone works more than 7.6 hours a day, then the hours between 7.6 and 9.6 is paid at 1 1/2 times. This is summed up in the 1 1/2 times cell for the week.

if someone works more than 9.6 hours a day, then the hours after 9.6 needs to be summed up in the double time cell for the week.


The formula at the moment that is in the previous post does something close, but once they work over the 9.6 hours a day, the 1 1/2 column clears and the 2 hours or more goes automatically into the double time column. this is not quite right.

I hope this makes it clearer?

Any help would be great, as I am pretty much finished doing my timesheet.

Cheers Leon

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">Regular Hrs</td><td style="text-align: center;;">76</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF99;;">8.6</td><td style="text-align: center;;">7.6</td><td style="text-align: center;background-color: #CCFFCC;;">10.6</td><td style="text-align: center;;">7.6</td><td style="text-align: center;background-color: #FFFF99;;">8.6</td><td style="text-align: center;background-color: #FFFF99;;">8.6</td><td style="text-align: center;background-color: #CCFFCC;;">10.6</td><td style="text-align: center;background-color: #FFFF99;;">8.6</td><td style="text-align: center;;">7.6</td><td style="text-align: center;;">7.6</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">Time and 1/2 Hrs</td><td style="text-align: center;background-color: #FFFF99;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">Double Time Hrs</td><td style="text-align: center;background-color: #CCFFCC;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E21</th><td style="text-align:left">=SUM(<font color="Blue">G21:P21</font>)-E22-E23</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E22</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">G21:P21>7.6</font>), G21:P21-7.6</font>)-E23</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E23</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">G21:P21>9.6</font>), G21:P21-9.6</font>)</td></tr></tbody></table></td></tr></table><br />

 

[leoningold]

Thnkyou, thankyou, thankyou - AlphaFrog,

That is exactly what i was after!

I am very happy that you took the time to help me out.

Now I can wrap it up and so all is good!

All the best,

and thanks again.

Cheers Leon

 

[waltztj]

I need to add Sat and Sun to this can u help me out