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$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. |
#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$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. |
#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$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. . |
#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$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. |
#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 top-post is human, to bottom-post 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$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. . |
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 |