Getting Excel to return a blank field regardless of cell formatting

Hi all,

I currently have the following formula:

=IF($B$1=1,'4 Week Rota'!O13,IF($B$1=2,'4 Week Rota'!O21,IF($B$1=3,'4 Week Rota'!O29,IF($B$1=4,'4 Week Rota'!O37))))

When there is no value in 'Week Rota'!O13 Excel is returning 0:00 because the cell is formatted to [h]:mm. Is there a way for nothing to be returned regardless of format?

Secondly, I'm still rather a novice at Excel and am assuming there would be a much shorter (and tidier) version of the formula above?

Thanks in advance,

Luke

Hi lukela85,
See file attached.
Using CHOOSE function and custom number formatting [h]:mm;;

Hi,

A more efficient formula would be

Formula:

Please Login or Register  to view this content.

You could suppress the zero by using the Excel Options to NOT display 0 for cells that result in zero. Office Button..Excel Options..Advanced and scroll down to the 'Display Options for this worksheet'

Alternatively you could use a variation of the above

Formula:

Please Login or Register  to view this content.

but you may feel that's akin to using the proverbial sledgehammer to crack a nut!