#Value

Hello from Steved
Below gives me #Value

=IF(ISNA(IF(S60.6,S6-0.5,IF(S61/24,S5+0.5,S6)))&TEXT
(MINUTE(S6),"00"),"1200",HOUR(IF(S60.6,S6-0.5,IF
(S61/24,S6+0.5,S6)))&TEXT(MINUTE(S6),"00"))

But below works fine.

=HOUR(IF(S60.6,S6-0.5,IF(S61/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.

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")


--
Regards
Frank Kabel
Frankfurt, Germany


Steved wrote:
Hello from Steved
Below gives me #Value

=IF(ISNA(IF(S60.6,S6-0.5,IF(S61/24,S5+0.5,S6)))&TEXT
(MINUTE(S6),"00"),"1200",HOUR(IF(S60.6,S6-0.5,IF
(S61/24,S6+0.5,S6)))&TEXT(MINUTE(S6),"00"))

But below works fine.

=HOUR(IF(S60.6,S6-0.5,IF(S61/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.

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(S60.6,S6-0.5,IF(S61/24,S5+0.5,S6)))&TEXT
(MINUTE(S6),"00"),"1200",HOUR(IF(S60.6,S6-0.5,IF
(S61/24,S6+0.5,S6)))&TEXT(MINUTE(S6),"00"))




-----Original Message-----
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")


--
Regards
Frank Kabel
Frankfurt, Germany


Steved wrote:
Hello from Steved
Below gives me #Value

=IF(ISNA(IF(S60.6,S6-0.5,IF(S61/24,S5+0.5,S6)))&TEXT
(MINUTE(S6),"00"),"1200",HOUR(IF(S60.6,S6-0.5,IF
(S61/24,S6+0.5,S6)))&TEXT(MINUTE(S6),"00"))

But below works fine.

=HOUR(IF(S60.6,S6-0.5,IF(S61/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.

.