[Gllug] OpenOffice Calc date formats

[Justin Perreault]

On Fri, 2009-06-26 at 11:35 +0100, lesleyb at herlug.org.uk wrote:
> Hi
> 
> Hope this is reasonably on-topic.
> 
> I downloaded a csv file with dates in the following format
> 
> Mon Jun 22 08:08:23 BST 2009
...
> I'll need to repeat this operation so I want to find a solution.
> I could do it nongraphically working solely on the csv file before it gets to
> Calc but I was wondering if anyone knows a date/time function that would convert 
> on the fly in Calc?

I usually end up doing stuff like this using Text Functions in Calc.

I am presuming that Jun 1st will show as Jun 1 not Jun 01. If the
presumption is incorrect then the find-5 functions can be replaced with
the number 6.

>From your string if it is put into cell A2:
Date = Jun 22 2009
=MID(A2;5;FIND(" ";A2;9)-5)&" "&RIGHT(A2;4)

Time = 08:08:23
=MID(A2;FIND(":";A2;1)-2;8)

Date and Time = Jun 22 2009 08:08:23
=MID(A2;5;FIND(" ";A2;9)-5)&" "&RIGHT(A2;4)&"
"&MID(A2;FIND(":";A2;1)-2;8)

I was pleasantly surprised to find that Calc will sort Date & Time in
the combined format for both day and time changes. . . and then I
realised I was wrong and it was just doing a text/numeric sorting(and I
that I should use larger data sets before opening my trap). Surrounding
the smaller equations with Datevalue() and Timevalue() will give time
specific sortable data.

Depending on exactly all the uses you plan for this data the above can
be played with.

I generally keep a spread sheet file with formulas that I use regularly
for a particular project. There is lots that can be done using the text
formulas.

I once created a set of spread sheet that did 5 Million+ date
comparisons took 10 mins to run and would crash the thin client session
if you closed them in the wrong order. I would not recommend it
generally, however the client was happy they could understand it and
tweak it. Yes I told them they should get a script written to do it,
they insisted and paid me, I was not one to argue at the time.

> Or do I need to write my own function/macro thing?

The other ways suggested should be pursued if you are inclined and
capable. They will give you much more power over files in general rather
than just spreadsheets.

Of course if you write your own macro you can probably submit it to OOo
and get it added for happiness of everyone.

Justin

-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug