develooper Front page | | Postings from October 2004

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

Thread Previous | Thread Next
Stephen Turner
October 26, 2004 06:56
RE: [OT] Excel Golf: Unix Time to Excel Time in one line
Message ID:
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:


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
  "Low Priced Cambridge Clare College. Big selection at eBay UK!"
  (Ad after Google search for Clare College Cambridge)

Thread Previous | Thread Next Perl Programming lists via nntp and http.
Comments to Ask Bjørn Hansen at | Group listing | About