#VALUE

[South]

Good morning,
Can anyone help please. I have the following formula =H19-G19-IF((H19-G19)*60*24>=(10*60),(1/24),IF((H19-G19)*60*24>=(5*60),(0.5/24),0)) in a cell to calculate how many hours worked between start and finish time minus 30 minutes for each 5 hours worked. I have two issues

1. While the start and finish times are blank I get #VALUE but once I input times its ok. How can I make it show 0 instead of #VALUE
2. When I input times that go from one evening to the morning I get ####### (a negative number) how can I correct this?

Any help truly appreciated

 

[myall_blues]

Try this formula:

=LET(tc,IF(H19<G19,TODAY()+1+H19-G19,H19-G19),
tc-INT(tc/(5/24))*0.5/24)

 

[South]

Thanks for your reply the formula has worked and fixed the negative number issue.

Is there a way of hiding the #VALUE! when start and finish time are blank?

 

[myall_blues]

Weirdly it doesn’t give me an error - perhaps your 'blanks' aren’t really blank but rather contain spaces or something?
Anyway, you can wrap the whole thing in an IFERROR function.

=IFERROR(LET(tc,IF(H19<G19,TODAY()+1+H19-G19,H19-G19),
tc-INT(tc+H20/(5/24))*0.5/24),0)

 

[South]

I have resolved the #VALUE! by ADDING IF ERROR

=IFERROR(LET(tc,IF(H9<G9,TODAY()+1+H9-G9,H9-G9),tc-INT(tc/(5/24))*0.5/24),"")

Thanks again myall_blues for your help with the formula.

 

[myall_blues]

You’re welcome. I’m glad it worked.