Abend!

Neulich auf der Perl Golf Liste fand sich folgendes (nur so zum
aufwärmen, bevor wir demnächst die .NET Golfschläger auspacken):

------- Forwarded message follows -------
Subject:         [OT] Excel Golf: Unix Time to Excel Time in one line
Date sent:       Tue, 26 Oct 2004 13:58:04 +0100
From:           "McGlinchy, Alistair" <Alistair.McGlinchy@mark...>
To:             <golf@perl...>

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
)







------- End of forwarded message -------

Tja, kein VB, nur Excelfunktionen. Lösungen finden sich im Archiv der
Liste - nicht schummeln, gell?
Viel Spass (soweit mit Excel möglich),

             Bernd


        "If at first you don't succeed... So much for skydiving." -
Henry Youngman.


_______________________________________________
Golf.asp Mailingliste, Postings senden an:
Golf.asp@glen...
An-/Abmeldung und Suchfunktion unter:
http://www.glengamoi.com/mailman/listinfo/golf.