Blank Cell

Hello from Steved
The formula below works,

=HOUR(IF($T$40.5,$T$4-0.5,IF($T$41/24,$T$4+0.5,$T$4)))
&TEXT(MINUTE($T$4),"00"))

but when I add IF(ISNUMBER($C$4),the cells go blank which
is what I require, however when I put in a value, it stays
blank. Is their another solution to blank the cell please
as there are over 900 cells which I need to have blank if
there is no value to go in. (It is showing 1200, until i
enter a value.)

=IF(ISNUMBER($C$4),HOUR(IF($T$40.5,$T$4-0.5,IF
($T$41/24,$T$4+0.5,$T$4)))&TEXT(MINUTE($T$4),"00"))
Thankyou.

Do you want entries in both C4 AND T4 then use

=IF(AND(ISNUMBER($C$4),ISNUMBER($T$4)),HOUR(IF($T$ 40.5,$T$4-0.5,IF($T$41/2
4,$T$4+0.5,$T$4)))&TEXT(MINUTE($T$4),"00"),"")

if you want entries in either C4 OR T4 use

=IF(OR(ISNUMBER($C$4),ISNUMBER($T$4)),HOUR(IF($T$4 0.5,$T$4-0.5,IF($T$41/24
,$T$4+0.5,$T$4)))&TEXT(MINUTE($T$4),"00"),"")

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



wrote in message
...
Hello from Steved
The formula below works,

=HOUR(IF($T$40.5,$T$4-0.5,IF($T$41/24,$T$4+0.5,$T$4)))
&TEXT(MINUTE($T$4),"00"))

but when I add IF(ISNUMBER($C$4),the cells go blank which
is what I require, however when I put in a value, it stays
blank. Is their another solution to blank the cell please
as there are over 900 cells which I need to have blank if
there is no value to go in. (It is showing 1200, until i
enter a value.)

=IF(ISNUMBER($C$4),HOUR(IF($T$40.5,$T$4-0.5,IF
($T$41/24,$T$4+0.5,$T$4)))&TEXT(MINUTE($T$4),"00"))
Thankyou.

Thankyou Peo

-----Original Message-----
Do you want entries in both C4 AND T4 then use

=IF(AND(ISNUMBER($C$4),ISNUMBER($T$4)),HOUR(IF
($T$40.5,$T$4-0.5,IF($T$41/2
4,$T$4+0.5,$T$4)))&TEXT(MINUTE($T$4),"00"),"")

if you want entries in either C4 OR T4 use

=IF(OR(ISNUMBER($C$4),ISNUMBER($T$4)),HOUR(IF
($T$40.5,$T$4-0.5,IF($T$41/24
,$T$4+0.5,$T$4)))&TEXT(MINUTE($T$4),"00"),"")

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



wrote in message
...
Hello from Steved
The formula below works,

=HOUR(IF($T$40.5,$T$4-0.5,IF
($T$41/24,$T$4+0.5,$T$4)))
&TEXT(MINUTE($T$4),"00"))

but when I add IF(ISNUMBER($C$4),the cells go blank
which
is what I require, however when I put in a value, it
stays
blank. Is their another solution to blank the cell
please
as there are over 900 cells which I need to have blank
if
there is no value to go in. (It is showing 1200, until
i
enter a value.)

=IF(ISNUMBER($C$4),HOUR(IF($T$40.5,$T$4-0.5,IF
($T$41/24,$T$4+0.5,$T$4)))&TEXT(MINUTE($T$4),"00"))
Thankyou.


.

"Peo Sjoblom" wrote...
Do you want entries in both C4 AND T4 then use

=IF(AND(ISNUMBER($C$4),ISNUMBER($T$4)),HOUR(IF($T $40.5,$T$4-0.5,IF($T$41/2
4,$T$4+0.5,$T$4)))&TEXT(MINUTE($T$4),"00"),"")

Could be shortened to

=IF(COUNT($C$4,$T$4)=2,SUBSTITUTE(LEFT(TEXT($T$4," h:mm AM/PM"),5),":",""),"")


if you want entries in either C4 OR T4 use

=IF(OR(ISNUMBER($C$4),ISNUMBER($T$4)),HOUR(IF($T$ 40.5,$T$4-0.5,IF($T$41/24
,$T$4+0.5,$T$4)))&TEXT(MINUTE($T$4),"00"),"")

Could be shortened to

=IF(COUNT($C$4,$T$4),SUBSTITUTE(LEFT(TEXT($T$4,"h: mm AM/PM"),5),":",""),"")

--
To top-post is human, to bottom-post and snip is sublime.

"Harlan Grove" wrote...
...
=IF(COUNT($C$4,$T$4)=2,SUBSTITUTE(LEFT(TEXT($T$4, "h:mm AM/PM"),5),":",""),"")
...
=IF(COUNT($C$4,$T$4),SUBSTITUTE(LEFT(TEXT($T$4,"h :mm AM/PM"),5),":",""),"")

Wrap the SUBSTITUTE calls inside TRIM to eat possible trailing spaces.

--
To top-post is human, to bottom-post and snip is sublime.

Thankyou Harlan
-----Original Message-----
"Peo Sjoblom" wrote...
Do you want entries in both C4 AND T4 then use

=IF(AND(ISNUMBER($C$4),ISNUMBER($T$4)),HOUR(IF
($T$40.5,$T$4-0.5,IF($T$41/2
4,$T$4+0.5,$T$4)))&TEXT(MINUTE($T$4),"00"),"")

Could be shortened to

=IF(COUNT($C$4,$T$4)=2,SUBSTITUTE(LEFT(TEXT($T$4, "h:mm
AM/PM"),5),":",""),"")


if you want entries in either C4 OR T4 use

=IF(OR(ISNUMBER($C$4),ISNUMBER($T$4)),HOUR(IF
($T$40.5,$T$4-0.5,IF($T$41/24
,$T$4+0.5,$T$4)))&TEXT(MINUTE($T$4),"00"),"")

Could be shortened to

=IF(COUNT($C$4,$T$4),SUBSTITUTE(LEFT(TEXT($T$4,"h :mm
AM/PM"),5),":",""),"")

--
To top-post is human, to bottom-post and snip is sublime.
.