Leaving a cell blank unless referenced cells contain a value

Hi all,

This is my first post to the forum, although I have been a long-time 'lurker'. The knowledge base here is pretty impressive, so I know I'm in good hands!

So here is my problem: I am working on a new time-card for our accounting team. I have the basic format laid out, and my formulas are working fine. However on my daily hours total it shows a '12' in that cell, when I would like to leave it blank. How can I leave this cell blank unless the cells that it references contain a value? My formula is below:

=IF(D16="","",((12-D16)+E16+G16+H16)-F16)

D16 = DAILY START TIME
E16 = DAILY END TIME
G16 = ANY VACATION HOURS TO BE USED
H16 = ANY PERSONAL HOURS TO BE USED
F16 = TOTAL TIME TAKEN FOR A MEAL PERIOD

Any assistance will be greatly appreciated.

Regards,

-dparkhill

Sorry about that.

The formula is in cell I16, which totals the hours worked during the week. and that is where I am getting the '12'.

I have attached a copy of my spreadsheet for reference - see below.

TIME CARD - REF.xlsx

Try this =IF(D2=0,0,((12-D2)+E2+G2+H2)-F2)

Thank you, everyone.

I implemented the 'OR' statement, and now the formatting looks great.

Thanks again!

- dparkhill

OK, so now the problem is that the spreadsheet is interpreting the time input incorrectly - 12pm for 12am, for instance. I tried changing the formatting from number to time, but that did not work. I also tried various adjustments to the formula, but again no luck.

I searched the Excel Forum database for help, but came up empty.

I have included a copy of the spreadsheet 'in use', so to speak as an example. Please see below.

TIME CARD - REF.xlsx

is this kind of what you are looking for?

It is, and it isn't...

I come from a solid military/law enforcement background, so I use a 24hr time format anyway - but the people I work with do not. Ergo, once I unleash the dreaded 'military time' on them, they will freak out.

I guess my real question now is why does Excel have such a hard time with 12pm? I know that the problem lies in how Excel is interpreting my formula (garbage in/garbage out), but I just cannot think of another way to draw up the formula...

would this be something better?

they would have to enter the time with the am and pm, and then figure out what decimal value is needed for their lunch/vacation/PTO is, but it also puts the total hours worked into decimal value which would make one less step in payroll.

Hi

if you set cells D17:I22 to custom format H:mm
Adjust the formula to =if(D17="",0(E17-D17)-F17+G17+H17)
Format the total in cell I23 to [h]:mm
this will set the time card to normal clock not 24 hour
thus 12 pm will be 12:00 and 12 am will be 00:00

hope this helps

All,

Thank you for taking the time to assist me with this.

I have attached a sanitized copy of the workbook for reference; In the final version, there will be validated data lists for employees, managers, and departments - I have removed those tabs from the workbook for this evolution. I have placed notes in column J for reference.

Cell C-D6 takes the week ending, and automatically populates cells C11-18, and C22-28 for the reporting period.

When I format the cell with custom format h:mm, I also get a date in the formula bar preceding the time.

Hi
Your time card is looking good

Just a thought to stop time being entered incorrectly, you could set up a data validation just for the start/finish times. the user would then just have to click on a start/finish cell and from the drop down list click on the time wanted. Have attached a small sample file for you to look at

if you need instructions to do this just give me a shout

Wow, that is pretty simple. Thank you!

I'm going to give that a shot. I will have to use 1/4 hours, but that will be fine.

I guess I have just been over-thinking this...