time formulas with blank cells

Microsoft Office excel 2003
July 25, 2010 at 17:21:34
Specs: Windows XP
 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

See More: time formulas with blank cells

#1
July 25, 2010 at 20:29:30
 you could try using "IF"e.g. =if(d3="",0,24*(TIME(7,0,0)-D3))Let me know if the same works.Thanks.

Report •

#2
July 25, 2010 at 20:41:34
 unfortunately it still returns 7 instead of 0.

Report •

#3
July 25, 2010 at 20:51:56
 Any other ideas on how to get it to work?

Report •

Related Solutions

#4
July 26, 2010 at 02:56:19
 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.

Report •

#5
July 26, 2010 at 04:40:33
 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

Report •

#6
July 26, 2010 at 16:44:11
 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!

Report •

#7
July 26, 2010 at 17:08:47
 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.

Report •

#8
July 27, 2010 at 04:03:48
 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 7AM``` B 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 7AM``` B 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 7AM``` B 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 0 ```Here are the two formulas:D5: =IF(OR(D3="",E3=""),0,IF(D3>TIME(7,0,0),0,IF(E3TIME(7,0,0),24*(E3-D3),IF(E3

Report •

#9
July 27, 2010 at 17:02:03
 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!

Report •

#10
July 28, 2010 at 16:13:27
 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?

Report •

#11
July 28, 2010 at 16:28:53
 Hi,If A2 contains the start time and B2 the end timethen 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 wantRegards

Report •

#12
July 28, 2010 at 16:40:26
 Thankyou! I want to learn more about formulas so I can just work them out easily like you do!

Report •

#13
July 28, 2010 at 17:45:24
 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

Report •

#14
July 28, 2010 at 19:52:50
 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(,"Morning","Not morning")Then I can test my morning /not morning formulae.g. if A1 is start time:If(A1time(12,0,0), "Afternoon", "not afternoon")RegardsPS I've been 'doing' Excel formulas for a long time - I still have my Excel 5 installation disks, and started on Excel 4

Report •

#15
July 28, 2010 at 21:18:02
 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

Report •