Formula Substitute Help

Hi All 

 

Im wondering if somebody can help I am using the below formula to calculate hours it all works apart from if a cell is empty for example: 

 

Cell B4 (08:00 – 20:00) Cell C4 (08:00 – 20:00) Cell D4 produces the number 24 which is correct but if the below happens

 

Cell B4 (08:00 – 20:00) Cell C4 (“”) Cell D4 (Then shows Value) as there is an error where it is not picking up an empty cell as a 0 I would want cell D4 to produce the number 12 in this scenario. 

 

I tried to use an IF value within the below but cant figure out how to get it to work

 

=((SUBSTITUTE(B4,LEFT(B4,FIND(“-“,B4)+1),””)*1-SUBSTITUTE(B4,RIGHT(B4,FIND(“-“,B4)-1),””)*1)+(SUBSTITUTE(B4,LEFT(B4,FIND(“-“,B4)+1),””)*1<SUBSTITUTE(B4,RIGHT(B4,FIND(“-“,B4)-1),””)*1))*24+((SUBSTITUTE(C4,LEFT(C4,FIND(“-“,C4)+1),””)*1-SUBSTITUTE(C4,RIGHT(C4,FIND(“-“,C4)-1),””)*1)+(SUBSTITUTE(C4,LEFT(C4,FIND(“-“,C4)+1),””)*1<SUBSTITUTE(C4,RIGHT(C4,FIND(“-“,C4)-1),””)*1))*24

 

If someone could help I would be so happy. 

 

Finnstina

By: Finnstina

Leave a Reply

Your email address will not be published. Required fields are marked *