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:
Gerber, Christopher J
Date:
October 26, 2004 09:33
Subject:
RE: [OT] Excel Golf: Unix Time to Excel Time in one line
Message ID:
DAE79A519D0ECB4DA2E3864CDB61A4E7D31AC3@groamrexm01.amer.pfizer.com
> -----Original Message-----
> From: McGlinchy, Alistair 
> 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)-WEEKDA
Y(DATE(YEA
> R(25569+A4/86400),5,1),2)+1/24<25569+A4/86400,25569+A4/86400<D
ATE(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.
> 

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(YEAR(25569+A4/86
400),5,1),2)+1/24<25569+A4/86400)*(25569+A4/86400<DATE(YEAR(25569+A4/86400),
11,1)-WEEKDAY(DATE(YEAR(25569+A4/86400),11,1),2)+1/12)/24


LEGAL NOTICE
Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this E-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents of this E-mail or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.

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