Formula to calculate hours and ignore text Document Attached

[keehinlomax]

Hi Guys,

I have an excel sheet for my staff at work, now the cells calculate automatically the worked hours based on a time entry.

When i have somebody off or on holiday etc i want to be able to just type it in the cell, however due to the current formula i get #value! error in the calculation cell which throws everything off.

How can i generate a formula to ignore a cell and leave it at 0.0 if i have a text entry?

I have attached a sample document for you to have a look at how it's donev2.xls

[cyiangou]

Enter this formula in cell D12, and copy across to J12:

Please Login or Register  to view this content.

Now copy this range D12:J12, to each of the other Hours Worked rows in the table.

[Glenn Kennedy]

If you are using Excel 2013, put this in D18 and save files as XLSX.
=IFERROR((D17-D16)*24,"")
If you need Excel 2003 backward compatability, use this and save as XLS
=IF(ISERROR((D17-D16)*24),"",(D17-D16)*24)

Use the same error trap in ALL other similar formulae.

[keehinlomax]

Thanks guys,

Its worked a treat.. Saved me a lot of writing by hand!

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.