Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Hans
 
Posts: n/a
Default error message ISBLANK

Hello

Im using the following formula for date calculation:

=IF(ISBLANK(C38),"",IF(C38=38807,"",(EOMONTH(C38,0 ))))

If C38 is blank or contains the date 31-mar-06 (38807) it returns a blank,
otherwise it should enter the last day of the month mentioned in C38.

It works OK if it refers to a real blank cell (with no formula) or to a cell
that contains a date. However, when it refers to a cell that contains above
formula (and returned a blank) I get a #VALUE error message. For example, if
above formula would be in Cell C39 and would return a blank, i would get an
error message if I copied the formula to Cell C40 (referring to Cell C39).

Does anyone know how to solve this? I want to copy this formula down without
getting error message.

Thanks in advance.
Hans


  #2   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

Hi

Try
=IF(OR(C38="",C38=38807),"",EOMONTH(C38,0))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Hans" wrote in message
...
Hello

Im using the following formula for date calculation:

=IF(ISBLANK(C38),"",IF(C38=38807,"",(EOMONTH(C38,0 ))))

If C38 is blank or contains the date 31-mar-06 (38807) it returns a blank,
otherwise it should enter the last day of the month mentioned in C38.

It works OK if it refers to a real blank cell (with no formula) or to a

cell
that contains a date. However, when it refers to a cell that contains

above
formula (and returned a blank) I get a #VALUE error message. For example,

if
above formula would be in Cell C39 and would return a blank, i would get

an
error message if I copied the formula to Cell C40 (referring to Cell C39).

Does anyone know how to solve this? I want to copy this formula down

without
getting error message.

Thanks in advance.
Hans




  #3   Report Post  
Hans
 
Posts: n/a
Default

Hi Arvi

It helped (Do you know why?)

Thanks!


Hans


"Arvi Laanemets" wrote:

Hi

Try
=IF(OR(C38="",C38=38807),"",EOMONTH(C38,0))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Hans" wrote in message
...
Hello

Im using the following formula for date calculation:

=IF(ISBLANK(C38),"",IF(C38=38807,"",(EOMONTH(C38,0 ))))

If C38 is blank or contains the date 31-mar-06 (38807) it returns a blank,
otherwise it should enter the last day of the month mentioned in C38.

It works OK if it refers to a real blank cell (with no formula) or to a

cell
that contains a date. However, when it refers to a cell that contains

above
formula (and returned a blank) I get a #VALUE error message. For example,

if
above formula would be in Cell C39 and would return a blank, i would get

an
error message if I copied the formula to Cell C40 (referring to Cell C39).

Does anyone know how to solve this? I want to copy this formula down

without
getting error message.

Thanks in advance.
Hans





  #4   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

Hi


"Hans" wrote in message
...
Hi Arvi

It helped (Do you know why?)


When there is formula in cell, then the cell isn't blank. Logically your
formula tried to calculate EOMONTH("",0) which returned an error.
In my formula, when there is a formula in C38, the value returned by formula
is checked. When formula in C38 returns "", then IF returns "".
When there isn't anything in C38 - i.e. cell is empty, then in condition
check Excel iterprets it as empty string, and logically IF again returns "".
But you get an error, when C38 contains p.e. "xxxx". You can consider the
need for further checks, p.e.
=IF(OR(C38="",ISTEXT(C38),C38=38807,C38<0,C382957 978),"",EOMONTH(C38,0))
Last 2 conditions are checking for negative dates (not allowed when 1900
date system is used) and for numbers too big to be converted to dates by
EOMONTH function.


--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


  #5   Report Post  
Hans
 
Posts: n/a
Default

Hello

Thanks for your explanation; makes things a bit more easier to understand.

Regards
Hans

"Hans" wrote:

Hello

Im using the following formula for date calculation:

=IF(ISBLANK(C38),"",IF(C38=38807,"",(EOMONTH(C38,0 ))))

If C38 is blank or contains the date 31-mar-06 (38807) it returns a blank,
otherwise it should enter the last day of the month mentioned in C38.

It works OK if it refers to a real blank cell (with no formula) or to a cell
that contains a date. However, when it refers to a cell that contains above
formula (and returned a blank) I get a #VALUE error message. For example, if
above formula would be in Cell C39 and would return a blank, i would get an
error message if I copied the formula to Cell C40 (referring to Cell C39).

Does anyone know how to solve this? I want to copy this formula down without
getting error message.

Thanks in advance.
Hans


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 04:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"