Microsoft Office excel 2003

Hello, can anyone help me? I have a payroll formula that I can't quite get right.

I want to be able to add the hours of before 7am. Eg. someone works from 6:00 am to 8:00 am.

My formula to work out how many hours are before 7am is:

=24*(TIME(7,0,0)-D3)

Where D3 is the start time eg. 6amThis formula works however when a person doesn't work it still comes up that they have worked 7 hours before 7am (7:00 - 0 because the cell is blank).

How can I improve this formula so that I get the correct amount of hours for when someone works and when they don't?

Please help me! Thanks

you could try using "IF"

e.g. =if(d3="",0,24*(TIME(7,0,0)-D3))Let me know if the same works.

Thanks.

unfortunately it still returns 7 instead of 0.

Any other ideas on how to get it to work?

If you use Milind's formula

=if(d3="",0,24*(TIME(7,0,0)-D3))

then the result will be 0 if there is nothing in Cell D3.You shouldn't get 7.

If you do, then your formula is probably not looking at the same cell, or it is taking another formula/value into account.

Hi, Milind's formula works if D3 is empty.

Are you sure that D3 is empty.

If D3 contains zero or could be empty then this would work:

=IF(OR(D3="",D3=0),0,24*(TIME(7,0,0)-D3))Regards

I don't understand why it doesn't work with Milinds formula but the cell is definietely blank. However, it does work with

=if(d3="","",24*(TIME(7,0,0)-D3))Thanks for your help guys!

I have my cells formatted to 24hr time and when i type in a time from before 7am it works. However, if I type in say 14:00- 21:00 it returns -7. How can I make it return 0 or have it blank rather than return a negative figure because you can't work -7 hrs.

Hi, I am assuming that you want two formulas which allow you to calculate hours before and hours after 7AM.

The formulas should return zero if no hours entered, and the formulas should work for start times after 7AMHere are four examples of outputs:

1. Start is before 7AMB C D E 2 Start End 3 6:00 10:00 4 Rate Hours Pay 5 pre-7AM 19.00 1.00 19 6 post-7AM 19.25 3.00 57.75

2. Start is after 7AMB C D E 2 Start End 3 14:00 21:00 4 Rate Hours Pay 5 pre-7AM 19.00 0.00 0 6 post-7AM 19.25 7.00 134.75

3. Start and End time both before 7AMB C D E 2 Start End 3 3:00 6:00 4 Rate Hours Pay 5 pre-7AM 19.00 3.00 57 6 post-7AM 19.25 0.00 0

4. No start or finish time:B C D E 2 Start End 3 4 Rate Hours Pay 5 pre-7AM 19.00 0.00 0 6 post-7AM 19.25 0.00 0Here are the two formulas:

D5: =IF(OR(D3="",E3=""),0,IF(D3>TIME(7,0,0),0,IF(E3<TIME(7,0,0),24*(E3-D3),24*(TIME(7,0,0)-D3))))

D6: =IF(OR(D3="",E3=""),0,IF(D3>TIME(7,0,0),24*(E3-D3),IF(E3<TIME(7,0,0),0,24*(E3-TIME(7,0,0)))))You haven't said if anyone starts work before midnight and ends work after midnight.

These formulas will not be correct in that situation.Regards

Humar- You are a legend!!! Yes that works, I don't need to worry about the crossing over of midnight so this formula will do.

Thankyou so much for all your help!

If I want to have a rate for 5 hours and then a penalty rate for every hour worked after that do I have to define a time this 5hrs (eg 11 am) is or is there a formula that I can put in so that no matter when they start or finish it will calculate the first five hours and the remainder penalty hours seperately?

Hi, If A2 contains the start time and B2 the end time

then display standard hours in B3 with this formula:

=IF(B2-A2>TIME(5,0,0),5,24*(B2-A2))

and penalty hours in B4 with this formula:

=IF(B2-A2<=TIME(5,0,0),0,24*(B2-A2)-5)Hope this does what you want

Regards

Thankyou! I want to learn more about formulas so I can just work them out easily like you do!

Can I add an IF statement to the formulaso that if it is a morning shift eg. 7:45-2pm the penalties will go in one cell and if it is an evening shift eg. 2-9pm the penalties will go in another shift? Penalties are different dependant on if its in the morning or at night

Hi, Yes you can wrap these formulas in further IF statements.

Before you do, try making two pairs of formulas - one pair works for morning shifts, and one pair works for afternoon shifts.

Once each pair work for times before or times after the cutoff, then wrap them in an IF statement that returns zero if the time is not appropriate.

I like to do an if statement in steps:

IF(<start time is morning>,"Morning","Not morning")

Then I can test my morning /not morning formula

e.g. if A1 is start time:

If(A1<time(12,0.0),"Morning","Not morning")

When this bit is working add one of the two options, in this case the easy bit is to replace "Not morning" with 0.

then I can add my morning calculation, and I know that any errors are due to the morning calculation and not the IF part of the formula.In the case of your morning shift: 07:45 to 14:00, you have to decide which is more significant - is it a start time before 14:00 or a start time equal to 07:45, or is it any shift that starts before say 12:00.

Consider what start and finish times get used, what about someone who starts at say 08:00.

So let's say a morning shift is any shift starting before 12:00

the IF statement is:

If(A1<=time(12,0,0), "Morning","not morning")

You can use this IF framework for both the standard hours formula and the penalty hour formula.

Then reverse it for the afternoon shifts:

if(A1>time(12,0,0), "Afternoon", "not afternoon")Regards

PS I've been 'doing' Excel formulas for a long time - I still have my Excel 5 installation disks, and started on Excel 4

Thankyou Humar! I have worked it out using the information you gave me. I appreciate all your help. If your not a teacher already, you should be! Thanks again

Ask Your Question

Weekly Poll