develooper Front page | perl.golf | Postings from October 2004

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

Thread Previous | Thread Next
From:
McGlinchy, Alistair
Date:
October 26, 2004 06:47
Subject:
RE: [OT] Excel Golf: Unix Time to Excel Time in one line
Message ID:
8B653E54B22CC741B9D5AF5FD15126FCA1C44B@MSHVSMNSEXP0002.mnsexchange.adroot.marksandspencer.com

From: Stephen Turner:
> On Tue, 26 Oct 2004, McGlinchy, Alistair wrote:
> > Here's a horrid 297 Byte solution:

> I don't know much about Excel, but I think DATE(1970,1,1) can 
> be spelled 25569. If I'm right, that would save you at least 
> 63 strokes.
> 

I feel suitably humbled. Thanks to you and Joe for some significant
improvements.

=25569+A4/86400+1/24*AND(DATE(YEAR(25569+A4/86400),5,1)-WEEKDAY(DATE(YEA
R(25569+A4/86400),5,1),2)+1/24<25569+A4/86400,25569+A4/86400<DATE(YEAR(2
5569+A4/86400),11,1)-WEEKDAY(DATE(YEAR(25569+A4/86400),11,1),2)+2/24)

With only 214 characters, the formula is only two lines on my screen so
I can see my column headings (but not Excel's column head.

Yippee.

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. 


Thread Previous | Thread Next


nntp.perl.org: Perl Programming lists via nntp and http.
Comments to Ask Bjørn Hansen at ask@perl.org | Group listing | About