If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Blank Cell
Hello from Steved
The formula below works, =HOUR(IF($T$40.5,$T$40.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$40.5,IF ($T$41/24,$T$4+0.5,$T$4)))&TEXT(MINUTE($T$4),"00")) Thankyou. 
Ads 
#2




Blank Cell
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$40.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$40.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$40.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$40.5,IF ($T$41/24,$T$4+0.5,$T$4)))&TEXT(MINUTE($T$4),"00")) Thankyou. 
#3




Blank Cell
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$40.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$40.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$40.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$40.5,IF ($T$41/24,$T$4+0.5,$T$4)))&TEXT(MINUTE($T$4),"00")) Thankyou. . 
#4




Blank Cell
"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$40.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$40.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 toppost is human, to bottompost and snip is sublime. 
#5




Blank Cell
"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 toppost is human, to bottompost and snip is sublime. 
#6




Blank Cell
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$40.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$40.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 toppost is human, to bottompost and snip is sublime. . 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Finding Next Blank Cell  JaiD  Worksheet Functions  1  June 13th, 2004 10:18 AM 
Formula with a blank cell  Bennos  Worksheet Functions  5  March 25th, 2004 12:45 AM 
How to tell the difference between a zero and a blank cell value  Slider  Worksheet Functions  2  March 16th, 2004 08:17 PM 
0 if blank  Theo  Worksheet Functions  7  January 7th, 2004 08:20 PM 