Replacing #VALUE! with a blank in a Formula

[Magalela]

Good morning.
I have created a daily time calculator formula between two different times. However, on my table the formula should exclude Sundays.
The problem that I am having is that when the date falls on a Sunday the time difference result becomes #VALUE!. Is there a way that excel could give a blank on the result?
I have attached my spreadsheet for reference.

[HDHP]

You can try IFERROR function.

[sandy666]

or change "" to 0 in D and C column

Please Login or Register  to view this content.

[Magalela]

or change "" to 0 in D and C column

Please Login or Register  to view this content.

This formula only works where the result is Zero. But where the result is not a Zero it gives an incorrect result of 00.29 instead of 08.00 as can be seen from the attached spreadsheet.

[Magalela]

You can try IFERROR function.

This function yields the same error result.

[HansDouwe]

If you try =IFERROR(--IF((C5-B5+(C5<B5))*24<>0,((C5-B5+(C5<B5))*24-1)),"") it returns no #errors, but blanks instead of

[sandy666]

column B

Please Login or Register  to view this content.

Column C

Please Login or Register  to view this content.

Column D

Please Login or Register  to view this content.

[sandy666]

if you see something different than 08:00 or 07:00 change format of cells (format painter)
anyway if G1 = 07:00 so result will 07:00 not 08:00

you can try this

Please Login or Register  to view this content.

but if G1 = 07:00 result will be 07:00

or maybe

Please Login or Register  to view this content.

result: 08:00
with format painter from I1

I don't understand your logic, instead of subtracting C-B columns you copy values from cell G1, so why all these formulas?

[Magalela]

If you try =IFERROR(--IF((C5-B5+(C5<B5))*24<>0,((C5-B5+(C5<B5))*24-1)),"") it returns no #errors, but blanks instead of

Thanks. This works perfectly.

[Magalela]

Thank you so much. it worked.

[HansDouwe]

Glad to have helped. You are welcome, thanks for the feedback and rep .