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.
