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:
Stephen Turner
Date:
October 26, 2004 06:56
Subject:
RE: [OT] Excel Golf: Unix Time to Excel Time in one line
Message ID:
Pine.LNX.4.58.0410261452170.3928@gentoo
On Tue, 26 Oct 2004, McGlinchy, Alistair wrote:
>
> 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.
>

I've been looking into this a bit more.

If you want the time in GMT, it's easy:

=25569+A2/86400

and apply whatever format you want to the cell.

I don't know if you can have the time in other timezones though. Excel
doesn't seem to know about them, unless I'm missing something. I set my
clock to Pacific time, and NOW() changed to 38286.29 (i.e. 29% of the way
through today), and 1/1/1970 00:00:00 was still 25569.00.

-- 
Stephen Turner, Cambridge, UK    http://homepage.ntlworld.com/adelie/stephen/
  "Low Priced Cambridge Clare College. Big selection at eBay UK!"
  (Ad after Google search for Clare College Cambridge)

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