Front page | perl.golf | Postings from October 2004

## RE: [OT] Excel Golf: Unix Time to Excel Time in one line

From:
McGlinchy, Alistair
Date:
October 28, 2004 07:00
Subject:
RE: [OT] Excel Golf: Unix Time to Excel Time in one line
Message ID:
```> From: Gerber, Christopher J [mailto:Christopher.J.Gerber@pfizer.com]
> 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
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.

```