Excel Quirk or my ignorance?

  • Thread starter Thread starter chickooooos
  • Start date Start date
C

chickooooos

Hi

I have a cell with a julian date code written as below in A1:

3-365 (Formatted as General).

Where 3 represent year 2003 and 365 represent 365th day from 1st of Jan. For
a validation purpose I need to make sure when someone enter a 3-366 (366 not
possible as 2003 is not a leap year), it shows an error message.

I have in B1 (formatted as Date) following formula.

B1 ="1-Jan-200"&LEFT(A1,1)

to display the January 1st of the year entered.

On C1 (formatted as Number) I have the another formula to to check the year
is a leap year and accordingly put maximum days allowed

C1 = IF(MOD(YEAR(B1),4)=0,366,365)

D1 = MID(A1,3,3) (to extract the date part say 366 If A1 is 3-366)
formatted as number

E1 = D1>C1

E1 is giving a incorrect results. Consider following scenario:

A1 = 3-200

then

B1 = 1-Jan-2003
C1 = 365
D1 = 200
E1 = TRUE

Where as E1 should be FALSE. IF I enter 200 directly in D1, it displays
FALSE, but not when the formula MID(A1,3,3) evaluate to 200!

Can someone explain this?

TIA
 
Change the formula in cell E1 to = Value(D1)>C1


Or change the formula in cell D1 in =VALUE(MID(A1,3,3))

The "problem" is that cell E1 in your example is containing a text rather
than a number.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Hi

I have a cell with a julian date code written as below in A1:

3-365 (Formatted as General).

Where 3 represent year 2003 and 365 represent 365th day from 1st of Jan. For
a validation purpose I need to make sure when someone enter a 3-366 (366 not
possible as 2003 is not a leap year), it shows an error message.

I have in B1 (formatted as Date) following formula.

B1 ="1-Jan-200"&LEFT(A1,1)

to display the January 1st of the year entered.

On C1 (formatted as Number) I have the another formula to to check the year
is a leap year and accordingly put maximum days allowed

C1 = IF(MOD(YEAR(B1),4)=0,366,365)

D1 = MID(A1,3,3) (to extract the date part say 366 If A1 is 3-366)
formatted as number

E1 = D1>C1

E1 is giving a incorrect results. Consider following scenario:

A1 = 3-200

then

B1 = 1-Jan-2003
C1 = 365
D1 = 200
E1 = TRUE

Where as E1 should be FALSE. IF I enter 200 directly in D1, it displays
FALSE, but not when the formula MID(A1,3,3) evaluate to 200!

Can someone explain this?

TIA
Click to expand...

Well, D1 is text and C1 is a number.

So in D1 try = --MID(A1,3,3)

A few comments though, NONE of which apply if your limits will be 2000-2009,
but might be useful to you in the future.

1. Your algorithm for checking leap years, although it will probably work for
the range in which you have to check dates in the format you've got there, is
somewhat incomplete, in that centuries have to be divisible by 400, also. So
although 2000 is a leap year, 1900 and 2100 are not.

2. You are extracting the date using LEFT(A1,1) and MID(A1,3,3). This will
fail after 2009. If the "day" portion is always three digits (i.e. 3-001),
then you could use RIGHT(A1,3). Otherwise you could "FIND" the "-" in the
string and decide on the length of the argument. For the Year, FIND'ing the
"-" is probably best.

3. If you do not restrict the entry format to represent the day as 3 digits,
then there will be entries that XL interprets differently. For example 3-1
would be interpreted as March 1, 2003 (or whatever the current year is), and
not 2003-Jan-1. You may be able to prevent this by pre-formatting the cell as
text.

Another way of determining if the year is a leap year might be:

=DAY(DATE(LEFT(A1,FIND("-",A1)-1)+2000,2,29))

That will equal 29 if it is a leap year, or 1 if it is not. And it will be
accurate except for 1900 which XL thinks is a leap year. (I'm told this was
for compatibility with a bug in Lotus 123).

So, to get your 365/366 depending, you could also use:

=(DAY(DATE(LEFT(A1,FIND("-",A1)-1)+2000,2,29))=29)+365



--ron
 
Thanks Auk and Ron

I solved it with Value() function.

Thanks Ron for the additional comments. It is nice to learn from people like
you.

Regards
 
Thanks Auk and Ron

I solved it with Value() function.

Thanks Ron for the additional comments. It is nice to learn from people like
you.

Regards
Click to expand...


You're welcome.

By the way, a lot of the Y2K problem came about because folk who were writing
code in the 1970's (and later) NEVER considered that the code (which
represented the year with two digits) would still be being used after 1999 <g>.


--ron
 
Back
Top