> From: Gerber, Christopher J [mailto:[EMAIL PROTECTED]
> A couple more small changes:
>
> AND(X,Y) can be changed to (X)*(Y) in this case
>
> 1/24*X can be changed to X/24
>
> 2/24 can be changed to 1/12, although it's not shorter
>
> 86400/24 can be changed to 3600
>
> SO...
>
> =25569+(A4/3600+DATE(YEAR(25569+A4/86400),5,1)-WEEKDAY(DATE(YE
> AR(25569+A4/86400),5,1),2)+1/24<25569+A4/86400)*(25569+A4/8640
> 0<DATE(YEAR(25569+A4/86400),11,1)-WEEKDAY(DATE(YEAR(25569+A4
> /86400),11,1),2)+1/12)/24
For dates between excluding 30 Dec to 2 Jan)
YEAR(25569+A4/86400)
Is the same as (/secs per year)
A4/31556868+70
The AND always returns false on the error conditions and for ordinary
January and December values, which is what we want.
The day of the week of 1 May and 1 November is the same every 28 years
between 1970 and 2099.
DATE(A2/31556868+14,5,1)-WEEKDAY(date)+1/24<25569+A2/86400
DATE(A2/31556868+14,11,1)-WEEKDAY(date)+1/24<25569+A2/86400
Becomes
DATE(A2/31556868+14,5,1)-WEEKDAY(date)+1/24<5115+A2/86400
DATE(A2/31556868+14,11,1)-WEEKDAY(date)+1/24<5115+A2/86400
In fact within this range 31556868 approx = 316^3 is OK (75^4 doesn't
work)
DATE(A2/316^3+14,5,1)-WEEKDAY(date)+1/24<5115+A2/86400
DATE(A2/316^3+14,11,1)-WEEKDAY(date)+2/24<5115+A2/86400
I'm not to sure about the 86400/24 => 3600 tweak as
a + b/86400 + c*d/24 != a + (b/3600+c)*d/24
a + b/86400 + c*d/24 == a + (b/3600+(c)*(d))/24
Which requires two more () chars than it removes from 86400
Due to an absurdity of the Excel Date function
DATE(year,month,)
Is
DATE(year,month,1) -1
So 1 May can be expressed as
DATE(A2/316^3+14,5,)-WEEKDAY(DATE(A2/316^3+14,5,))+25/24
Hence so far my best score is 161 with:
= 25569+A2/86400+(
DATE(A2/316^3+14,5,)-WEEKDAY(DATE(A2/316^3+14,5,))+25/24
<
5115+A2/86400
)*(
5115+A2/86400
<
DATE(A2/316^3+14,11,)-WEEKDAY(DATE(A2/316^3+14,11,))+25/24
)/24
Surely there must be a more direct way of getting 1 May ? I played with
= (1 Jan )+ 121+(MOD(year,1)=0) but it never worked.
Cheers,
Alistair
-----------------------------------------------------------------------
Registered Office:
Marks and Spencer plc
Waterside House
35 North Wharf Road
London
W2 1NW
Registered No. 214436 in England and Wales.
Telephone (020) 7935 4422
Facsimile (020) 7487 2670
www.marksandspencer.com
Please note that electronic mail may be monitored.
This e-mail is confidential. If you received it by mistake, please let us know and
then delete it from your system; you should not copy, disclose, or distribute its
contents to anyone nor act in reliance on this e-mail, as this is prohibited and may
be unlawful.
The registered office of Marks and Spencer Financial Services PLC, Marks and Spencer
Unit Trust Management Limited, Marks and Spencer Life Assurance Limited and Marks and
Spencer Savings and Investments Limited is Kings Meadow, Chester, CH99 9FB. These
firms are authorised and regulated by the Financial Services Authority.
