Combining IF statement with regular formula...

**Story** · 07-23-2009, 10:09 AM

I am creating an electronic timesheet. I have created drop downs for times of the day, which then automatically calculates the number of hours worked based on the times selected. The trouble I am having is, I also need to incorporate "Vacation", "Sick Day", etc into the drop down box where the time of day is located.

The sheet works great until someone selects a non-time related answer, such as "Vacation". When this option is selected, the cell that is supposed to contain the number of hours worked says #VALUE! when I, in fact, need it to list a value of 0 for the number of hours worked. Any idea on how to combine my formula =(D6-D5)*24+(D8-D7)*24+(D10-D9)*24 with an IF statement that will automatically assign a value of 0 if a text answer is selected from the drop down?

Thank you in advance!

**NBVC** · 07-23-2009, 10:13 AM

=IF(ISNUMBER(X1),24*((D6-D5)+(D8-D7)+(D10-D9)),0)

Where X1 is the drop down cell... adjust as needed.

**Story** · 07-23-2009, 10:30 AM

=IF(ISNUMBER(D5),24*((D6-D5)+(D8-D7)+(D10-D9)),0) < This is a good start!

The formula above works if a person from 1st shift chooses a start time [represented in cell D5] that is non numerical, such as "vacation" or "sick time". I believe I now need to combine this with a conditional OR statement so that it can also apply to a person from 2nd or 3rd shift's start time [represented by D7 and D9, respectively].

What I REALLY need is for a cell containing text to be work a value of 0 because it is possible for someone to work hours in the morning, and then leave early as "sick" meaning the formula still needs to be able to calculate the number of hours actually worked. Right now, the formula would make the resulting calculation 0 if text is found anywhere in cells D5 through D10. I don't want to make the whole calculation 0 if text is found, but instead want just the cell containing text to be worth 0.

Clear as Mud?

**NBVC** · 07-23-2009, 11:49 AM

How about?

=IF(SUM(D5,D7,D9),24*((D6-D5)+(D8-D7)+(D10-D9)),0)

**Story** · 07-23-2009, 12:12 PM

That formula would not accomplish what I am attempting to do. It might be easier to answer if you can visualize the time sheet, which I have attached to this reply.

For instance, 3rd shift is from 11pm - 7am. So, Monday's and Tuesday's schedule would be as follows (with Vacation being taken for Tuesday night's shift)

Monday would be:

Cell D5 would say 12:00 AM
Cell D6 would say 07:00 AM
Cell D7 would be BLANK
Cell D8 would be BLANK
Cell D9 would say 11:00 PM
Cell D10 would be 11:59 PM
Cell D11's calculation would be: "8" (Hours)

Tuesday would be:

Cell F5 would say 12:00 AM
Cell F6 would say 07:00 AM
Cell F7 would be BLANK
Cell F8 would be BLANK
Cell F9 would say VACATION
Cell F10 would be BLANK
Cell F11's calculation would be: "7" (Hours)

Wednesday would be:
Cell H5 would say VACATION
Cell H6 would be BLANK
Cell H7 would be BLANK
Cell H8 would be BLANK
Cell H9 would say 11:00 PM
Cell H10 would be 11:59 PM
Cell H11's calculation would be: "1" (Hours)

Again, your help is GREATLY appreciated!