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:
Where D3 is the start time eg. 6am

This 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

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.

Report •

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

Report •

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

Report •

Related Solutions

July 26, 2010 at 02:56:19
If you use Milind's formula
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 •

July 26, 2010 at 04:40:33

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:


Report •

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

Thanks for your help guys!

Report •

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 •

July 27, 2010 at 04:03:48

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 7AM

Here 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
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(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.


Report •

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 •

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 •

July 28, 2010 at 16:28:53

If A2 contains the start time and B2 the end time
then display standard hours in B3 with this formula:
and penalty hours in B4 with this formula:

Hope this does what you want


Report •

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 •

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 •

July 28, 2010 at 19:52:50

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")


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

Report •

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 •

Ask Question