Front page | perl.golf | Postings from October 2004

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

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.

```