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

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

Thread Next
From:
McGlinchy, Alistair
Date:
October 26, 2004 05:58
Subject:
[OT] Excel Golf: Unix Time to Excel Time in one line
Message ID:
8B653E54B22CC741B9D5AF5FD15126FCA1C449@MSHVSMNSEXP0002.mnsexchange.adroot.marksandspencer.com
Golfers,

Please excuse the non-perl nature of this question. It's golf coding
question but in Excel not perl so LAMP's in the audience can press
delete now :-). I'm hoping some of you aliens out there have some tips
that could solve a real world problem.

Background:
I use Netflow/Flowtools and MRTG/RRD related CSV files that often
contain Unix time stamps (seconds since 1/1/1970  00:00 GMT). I use Perl
and Excel to mung these into something useful.  One of the constant
problems is the need to express these timestamps as dates in local time.
I have a native excel formula that works perfectly and is fast, but it
the very nasty side effect of being so long it fills wraps down three
lines to cover's the title rows of my spread sheet.   I spent a fair bit
of time golfing my solution but I got stuck a long way off one line:

Problem:
	Write an Excel function that takes a single cell (say "A2") and
converts it into a Excel time (days since 1/1/1900).

Rules:
	You can use any built in excel function
	You can't use a VB function, (they are incredibly slow copying
to 64000 rows and Excel isn't smart enough to know when to recalculate
or undo from them);
	You can't use hidden columns as temporary variables (I need a
clean import and export to CSV).
	I'm assuming GMT/BST as the time zones but I'd like the solution
to be generic enough to applicable to other time zones

Cheers,

Alistair



Here's a horrid 297 Byte solution:

=	DATE(1970,1,1)+ A2/24/60/60+1/24 *
	AND(
		DATE(YEAR(DATE(1970,1,1)+A2/24/60/60),5,1) 
			-
		WEEKDAY(DATE(YEAR(DATE(1970,1,1)+A2/24/60/60),5,1),2)
			+
		1/24
	<
		DATE(1970,1,1)+A2/24/60/60
	, 
		DATE(1970,1,1)+A2/24/60/60
	<
		DATE(YEAR(DATE(1970,1,1)+A2/24/60/60),11,1)
			-
		WEEKDAY(DATE(YEAR(DATE(1970,1,1)+A2/24/60/60),11,1),2)
			+
		1/24
	)







-----------------------------------------------------------------------


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 Next


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