Help with formula in time sheets

Legacy 180354 · May 12, 2011

I’m working on a time sheet and trying to figure out some formula. Besides for regular wages, we pay once a week an additional $15 per hour for the day that they worked the most hours.
So for example in Column A I put in the total hours worked per day:
1
2
3
3
In cell B1 I set up a formula (if(and(a1>=a2,a1>=3,a1>=4),a1*15,0)
In cell B2 I made (if(and(a2>=a1,a2>=a3,a2>=a4),a2*15,0)
And so on for all 4 cells.
My answers in column B was
0
0
45
45
Now, is there a way to do that the highest number should only calculate *15 once (since this bonus is only once a week)? I want it should look like this:
0
0
45
0
I’m using Microsoft XP professional and excel 2007.

Thank you and looking forward for a response!

dcardno · May 12, 2011

Why not use

Code:

=max(A1:A4) * 15

in cell A5?

alan.m · May 12, 2011

Hi bht,
I have a much more complicated answer.
Your data must start in Row 2,
First is to find the one cell with the maximum hours in column A:
="A"&MATCH(MAX(A:A),A:A,0)
(I put this in cell B1, you can't have it in Col A)
Next, a formula that you can use in col B, starting at row 2
=IF(ADDRESS(ROW(A2),1,4) = $B$1, A4*15,0)
Hope that helps

Legacy 180354 · May 12, 2011

Thanks so much for your answers. I will try it tomorrow since I closed the application for today and let you know how it goes!

Legacy 180354 · May 19, 2011

Hi Dean,
The reason your answer can't work for me is because after calculating which day they worked most hours, I need to go back to the corresponding week and subtract their regular working hours from the total regular hours - and add the total working hours that day as overtime hours.
(I also realized I made 2 mistakes 1- the bonus is only once a month, 2- the formula (if(and(a1>=a2,a1>=3,a1>=4),a1*15,0) was suppose to read (if(and(a1>=a2,a1>=a3,a1>=a4,a1>3),a1*15,0) since the bonus is only if they work at least 3 hours.) I'm sorry about the mistakes, I'm new here and still need to learn lots.
See below example:
<table border="0" cellpadding="0" cellspacing="0" width="332"><col style="width: 48pt;" width="64"> <col style="width: 23pt;" width="30"> <col style="width: 26pt;" width="35"> <col style="width: 27pt;" width="36" span="2"> <col style="width: 50pt;" width="67"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">
</td> <td style="width: 23pt;" width="30">Sun</td> <td style="width: 26pt;" width="35">Mon</td> <td style="width: 27pt;" width="36">Tues</td> <td style="width: 27pt;" width="36">Wed</td> <td style="width: 50pt;" width="67">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">week 1</td> <td align="right">2</td> <td align="right">1</td> <td align="right">3</td> <td align="right">3</td> <td align="right">3</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">week 2</td> <td align="right">1</td> <td align="right">2</td> <td align="right">3</td> <td align="right">0</td> <td align="right">3</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">week 3</td> <td align="right">2</td> <td align="right">1</td> <td align="right">2</td> <td align="right">0</td> <td align="right">2</td> </tr> </tbody></table>
In column F I have the Max hours per week.
In Column G I made the following formula (not shown above) (that was my initial question)=(IF(AND(F2>=F3,F2>=F4,F2>=3),F2*15,0))
then, when I need to add regular hours and overtime hours (for the 1st week) I did the following Formula:=SUM(B2:E2)+IF(G2>0,-F2,0)
then, when I added overtime hours per week I did
=IF(G2>0,F2,0)
for Week 1 it returned good (6 hours reg, 3 hours overtime) , but for week 2, since one day it he also worked 3 hours, it also returned as overtime pay(3 reg hrs, 3 overtime). for week 3 it also returned good (reg hrs5, 0 overtime).

I hope I'm all clear now, We're looking to give a bonus only once a month for the max hours(referred to as overtime hours) worked in one day,if it's more than 3 hours.
Alan, thanks for your message too. I'm still trying to figure out that one.

Legacy 180354 · May 25, 2011

Any ideas? (I know it's not like all complicated questions out there and I appreciate the time someone will take to help me!)

alan.m · May 25, 2011

You might try the following,
I have assumed there are 4 weeks, so my data looks like
<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64>
1
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2714809 width=64>Sun</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Mon</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Tues</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Wed</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>2 week 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>3 week 2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>4 week 3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>5 week 4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0</TD></TR></TBODY></TABLE>

Then, find the total hours and pay at, for example, $20/hour
next the hours in the day with the maximum hours AND if more then 3, pay $15/hour
Finally: total

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 38.25pt" height=51><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 38.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2714809 class=xl66 height=51 width=64>Total
Ordinary Hours</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=right>23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=right>460</TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 38.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=51 width=64>Total Overtime</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 align=right>3

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2714809 align=right>0</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 25.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=34 width=64>Total Pay</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>460</TD></TR></TBODY></TABLE>

Formulas:
Total Ordinary time =SUM(B2:E5)
ordinary time pay =B7*20
Total Overtime =IF(MAX(B2:E4)>3, MAX(B2:E4),0)
Overtime pay =B8*15
Total Pay =SUM(C7:C8)

Hope that helps

Legacy 180354 · May 25, 2011

Thanks for your response. Your answer is good, but as I mentioned before, I must do the totals separate for each week, and only for one time per these 4 weeks we pay overtime. week 1 I need ordinary pay and overtime, week 2 I need ordinary pay and overtime, and so on for all weeks. (only in 1 week it should show overtime)
Hope I'm clearer now.

alan.m · May 25, 2011

OK, how about in column G use the formulas
=IF(AND(F2>=3,F2=MAX(F2:F5),SUM(H1)=0),F2*15,0)
=IF(AND(F3>=3,F3=MAX(F2:F5),SUM(H1)=0),F3*15,0)
=IF(AND(F4>=3,F4=MAX(F2:F5),SUM(H1:H2)=0),F4*15,0)
=IF(AND(F5>=3,F5=MAX(F2:F5),SUM(H1:H3)=0),F5*15,0)

alan.m · May 25, 2011

Oops,
try
=IF(AND(F2>=3,F2=MAX(F2:F5),SUM(G1)=0),F2*15,0)
=IF(AND(F3>=3,F3=MAX(F2:F5),SUM(G1:G2)=0),F3*15,0)
=IF(AND(F4>=3,F4=MAX(F2:F5),SUM(G1:G3)=0),F4*15,0)
=IF(AND(F5>=3,F5=MAX(F2:F5),SUM(G2:G4)=0),F5*15,0)

Help with formula in time sheets

Legacy 180354

Guest

Excel Facts

dcardno

Well-known Member

alan.m

Board Regular

Legacy 180354

Guest

Legacy 180354

Guest

Legacy 180354

Guest

alan.m

Board Regular

Legacy 180354

Guest

alan.m

Board Regular

alan.m

Board Regular

Similar threads

Forum statistics

Share this page

Help with formula in time sheets

Legacy 180354

Guest

Excel Facts

dcardno

Well-known Member

alan.m

Board Regular

Legacy 180354

Guest

Legacy 180354

Guest

Legacy 180354

Guest

alan.m

Board Regular

Legacy 180354

Guest

alan.m

Board Regular

alan.m

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock