OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Changing the Internal Date Format

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Dana_Rea
General User
General User


Joined: 01 Nov 2005
Posts: 32

PostPosted: Mon Jan 30, 2006 6:03 am    Post subject: Changing the Internal Date Format Reply with quote

I have some data that I need to export to csv with a specific date format. Dates in Calc always seem to fall back to dd/mm/yy no matter what date values you enter into a cell, the formatting options you have choosen or Mandrakes Locale date setting.

How can I force Calc to store dates as YYYY-MM-DD (ISO)?
Back to top
View user's profile Send private message
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Mon Jan 30, 2006 6:57 am    Post subject: Reply with quote

Format of CSV exported dates depend on locale language settings.
Change it to Swedish to get an date exported as yyyy-mm-dd

Or change the column from a date (and it's representation) to a text column.

Does this help?
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
Back to top
View user's profile Send private message Visit poster's website
Dana_Rea
General User
General User


Joined: 01 Nov 2005
Posts: 32

PostPosted: Mon Jan 30, 2006 7:11 am    Post subject: Reply with quote

probe1 wrote:
Format of CSV exported dates depend on locale language settings.
Change it to Swedish to get an date exported as yyyy-mm-dd

Or change the column from a date (and it's representation) to a text column.

Does this help?

That is along the lines of what I'm thinking. Do you mean chaning to swedish by using:
Code:

export LC_TIME=en_SE

I don't seem to have the locale file for Sweden though, I guess I'd have to find a copy and compile it.

For the second point, when I override a date to text like:
'2005-01-01
It converts it to the numeric date value for some reason, i.e. 36422
I have around 30,000 dates to work with, so I guess i'd have to write some kind of macro to convert them all.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Jan 30, 2006 7:52 am    Post subject: Reply with quote

Have a look at Tools > Options > Lang
Ther are 3 lang-selections
1) UI (normally just the installed lang version),
2)Next level refers to the system-locale to be used (eg. which is the decimal separator when interpreting input),
3) is the default lang to be used for output when a part of the doc has no lang set explicitly.(having an option "This doc only")
4th level is within document, when setting the lang of some element explicitly.
I think you have to set the second lang.
No, I just tested with the default style of a new doc set to swedish. (Press F11 and find the default style of this doc or set the third tools-option to swedish).
Now the number 12.4 has a comma as decimal separator, which may be not what you want: 2006-01-12,"12,4"
Since you are using some kind of non-windows, you should use some combination GNU-tool (sort, wc, grep)or script-lang(perl,sed) to change one or more csv on the fly. Calc is not very flexible here.
Back to top
View user's profile Send private message
Dana_Rea
General User
General User


Joined: 01 Nov 2005
Posts: 32

PostPosted: Mon Jan 30, 2006 8:29 am    Post subject: Reply with quote

Thanks for the input, I have some csv processing code available so I can use that. I can also try to change the constraint on the date format that I will have to use.
Back to top
View user's profile Send private message
Dana_Rea
General User
General User


Joined: 01 Nov 2005
Posts: 32

PostPosted: Mon Jan 30, 2006 10:00 am    Post subject: Reply with quote

Arg, as usual there is a simple solution to this problem.
    1. Make the date look the way you want it.
    2. Copy it.
    3. Paste it into a text editor and save.
    4. Voila.


I love it when 3 hour problems have 1 minute solutions Rolling Eyes
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Jan 30, 2006 10:09 am    Post subject: Reply with quote

Well, there are folks out there who would disagree:
http://www.oooforum.org/forum/viewtopic.phtml?t=16480#122809

Rolling Eyes
Back to top
View user's profile Send private message
Dana_Rea
General User
General User


Joined: 01 Nov 2005
Posts: 32

PostPosted: Mon Jan 30, 2006 11:14 am    Post subject: Reply with quote

Well, I guess it depends how the editor handles the clipboard.
I've just been using KWrite, which seems to work ok when I want to permanently evaluate equations by cutting and pasting.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Jan 30, 2006 11:30 am    Post subject: Reply with quote

When I yank a spreadsheet-selection from system-clipboard into emacs, I get a perfect tab-delimited table.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group