Formula not allowing 00:00 to be blank?

[jonvanwyk]

Please Login or Register  to view this content.

The formula above is ment to return one of two different values. One type of value does math subtracting time to calculate the number of hours that applies to a particular label. The other value is if none of the labels apply, then the cell should simply reflect the value of D6 in time format.

So, you could have a result of a number, such as 4, or a result of time, such as 23:00. The problem I am having is if D6 is blank, it is returning a result of 00:00 (when the math calculating time is N/A due to a label not being selected). If D6 is blank, I need the cell containing the formula to also be blank.

Using the advanced options to deselect the box that allows a zero value to remain blank does not fix the problem. I am not sure why this is, but I suspect it is because of the last part of the formula. I should also note that the cell is formatted as "General".


Any help is greatly appreciated.

[davesexcel]

=IF(OR(E5="V1",E5="V2",E5="V3"),(D6-D5+(D6<D5))*24,TEXT(D6,"hh:mm"),"")
May be the solution

[jonvanwyk]

=IF(OR(E5="V1",E5="V2",E5="V3"),(D6-D5+(D6<D5))*24,TEXT(D6,"hh:mm"),"")

May be the solution

It says I have "entered too many arguments when I attempt your solution. Any other ideas? :-)

[martindwilson]

=IF(D6="","",IF(OR(E5="V1",E5="V2",E5="V3"),(D6-D5+(D6<D5))*24,TEXT(D6,"hh:mm")))

[jonvanwyk]

=IF(D6="","",IF(OR(E5="V1",E5="V2",E5="V3"),(D6-D5+(D6<D5))*24,TEXT(D6,"hh:mm")))

GENIUS! Yay!