Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date and If problem
I am trying to get a cell (B2) to auto fill if a cell on a 2nd
worksheet (H6) contains a date. I am using in B2 =IF(Report!H6 =" ";"";Report!H6) If there is a date in H6 then B2 correctly shows as the date, but if H6 is blank then B2 shows as 00January1900 I have B2 formatted as date, when i do not then the date shows as 465678. How can i resolve this problem, With thanks Bob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date and If problem
Hi Bob,
Am Thu, 21 Jun 2012 12:46:58 -0700 (PDT) schrieb Bob: I am using in B2 =IF(Report!H6 =" ";"";Report!H6) there is a typo in your formula. If Report!H6=" " means if Report!H6 contains a space. If Report!H6 is empty you get the value of an empty cell 0 and that's 00. January 1900. Try: =IF(Report!H6="","",Report!H6) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date and If problem
Claus Busch has brought this to us :
Hi Bob, Am Thu, 21 Jun 2012 12:46:58 -0700 (PDT) schrieb Bob: I am using in B2 =IF(Report!H6 =" ";"";Report!H6) there is a typo in your formula. If Report!H6=" " means if Report!H6 contains a space. If Report!H6 is empty you get the value of an empty cell 0 and that's 00. January 1900. Try: =IF(Report!H6="","",Report!H6) Regards Claus Busch To avoid ambiguity about cells being empty or not, I use... =NOT(Reort!H6) ...which evaluates to FALSE for the range if it's empty, meaning the return is TRUE for the formula. So... =IF(NOT(Report!H6),"",Report!H6) ...which translates that IF Report!H6 is empty THEN return an empty string ELSE return the value in Report!H6 Alternatively, to check if a cell is not empty... =IF(LEN(Report!H6),Report!H6,"") ...which translates that IF Report!H6 is NOT empty THEN return the value in Report!H6 ELSE return an empty string -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date and If problem
sorry about the typo:
To avoid ambiguity about cells being empty or not, I use... =NOT(Report!H6) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date and If problem
Hi Garry,
Am Thu, 21 Jun 2012 17:42:22 -0400 schrieb GS: To avoid ambiguity about cells being empty or not, I use... =NOT(Reort!H6) ..which evaluates to FALSE for the range if it's empty, meaning the return is TRUE for the formula. So... =IF(NOT(Report!H6),"",Report!H6) ..which translates that IF Report!H6 is empty THEN return an empty string ELSE return the value in Report!H6 Alternatively, to check if a cell is not empty... =IF(LEN(Report!H6),Report!H6,"") ..which translates that IF Report!H6 is NOT empty THEN return the value in Report!H6 ELSE return an empty string good point. I agree. Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date and If problem
On Jun 21, 10:51*pm, Claus Busch wrote:
Hi Garry, Am Thu, 21 Jun 2012 17:42:22 -0400 schrieb GS: To avoid ambiguity about cells being empty or not, I use... * =NOT(Reort!H6) ..which evaluates to FALSE for the range if it's empty, meaning the return is TRUE for the formula. So... * =IF(NOT(Report!H6),"",Report!H6) ..which translates that * IF Report!H6 is empty THEN * * return an empty string * ELSE * * return the value in Report!H6 Alternatively, to check if a cell is not empty... * =IF(LEN(Report!H6),Report!H6,"") ..which translates that * IF Report!H6 is NOT empty THEN * * return the value in Report!H6 * ELSE * * return an empty string good point. I agree. Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Thank you gentleman, help is much appreciated. Bob |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date and If problem
Claus,
I can't tell you the number of times I found people use the 'Spacebar' to 'clear contents' from cells. And then they wonder why their calc formulas suddenly return errors! Hence, I typically change things as noted to hopefully educate users (OR at the very least persuade them away from that bad habit).<g -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exel increment date problem wrt todays date. | Excel Worksheet Functions | |||
Date Formula Problem - Leave date blank if Null | Excel Worksheet Functions | |||
Us date versus EU date problem | Excel Programming | |||
Date problem | Excel Discussion (Misc queries) | |||
Date Problem | Excel Programming |