#Value

[Steved]

Hello from Steved
Below gives me #Value

=IF(ISNA(IF(S6>0.6,S6-0.5,IF(S6<1/24,S5+0.5,S6)))&TEXT
(MINUTE(S6),"00"),"1200",HOUR(IF(S6>0.6,S6-0.5,IF
(S6<1/24,S6+0.5,S6)))&TEXT(MINUTE(S6),"00"))

But below works fine.

=HOUR(IF(S6>0.6,S6-0.5,IF(S6<1/24,S6+0.5,S6)))&TEXT(MINUTE
(S6),"00")

Please can you tell what is that I am missing to the first
formula that will get it to function, as my objective is
that wiil the cell value is 1200 it is to be blank.

Thankyou.

 

[Frank Kabel]

Hi Steve
some questions:
- Why are you testing for 'ISNA'. Normally this error should not occur
(unless one of the cell references contain this error).
- what are the values in S6 and S5. any chance there're text values in
it?

In Addition. I would shorten the term
TEXT(MINUTE(S6),"00")

to
TEXT(S6,"mm")

 

[Steved]

Hello Frank.

Firstly Changing TEXT(MINUTE(S6),"00")to TEXT(S6,"mm")
gives the wrong Cell value.

Below is the formula in S5 formatted as Time ie 1:30 p.m.
=((TRUNC(R5)*60+(R5-TRUNC(R5))*100)*60)/86400

Hence I was trying to have a blenk cell as it is
displaying 1200 which is Cell C5 displaying th formula
=IF(ISNA(IF(S6>0.6,S6-0.5,IF(S6<1/24,S5+0.5,S6)))&TEXT
(MINUTE(S6),"00"),"1200",HOUR(IF(S6>0.6,S6-0.5,IF
(S6<1/24,S6+0.5,S6)))&TEXT(MINUTE(S6),"00"))