On 2010-11-26 17:13 Brian Barker wrote:
At 22:42 25/11/2010 -0500, Eustace Fril wrote:
Is there a way to set my own day of week abbreviation? Instead of Mon
Tue... I would rather have MO TU...
At 15:38 26/11/2010 -0500, Eustace Fril wrote:
... I have a column with dates, that use 1999-12-31 Fri in the Format
Cells - Numbers - Category:Date - Format field, with Format Code
YYYY-MM-DD NN. How do I change this to 1999-12-31 FR?
I guess I could change the format of the dates column to YYYY-MM-DD,
the add the double-letter date abbreviations on the next column, and
then merge each 2 cells on each line. But then I would have to do it
for each line, which is tiresome. Is there a way to expedite the process?
It may be a little easier than that:
o Somewhere out of the way - perhaps even on another sheet - create a
table with Sun, Mon, Tue, etc. in the first column and SU, MO, TU, etc,
in the second.
o Select the whole two by seven table and go to Insert | Names > |
Define... and give the table a name, e.g DayNames.
o Suppose your original dates are in column A. (The format of these
cells doesn't matter.) In B1 enter:
=TEXT(A1;"YYYY-MM-DD ")&VLOOKUP(TEXT(A1;"NN");DayNames;2;0)
o Now copy or fill this down column B.
o You can hide the original column A if desired.
How does this work? The first TEXT() reference produces a text string of
the date in YYYY-MM-DD format (but note also the trailing space in the
format). The second TEXT() reference produces just the day of the week
indicator - in the normal form - and the VLOOKUP() then uses this
indicator to look up your preferred form from the small table. The
ampersand concatenates the two parts of the date.
Note that the resulting value is a text string and cannot be used in
date calculations - but you have column A (even if hidden) for that.
I trust this helps.
Brian Barker
Yes, it works. I am not sure, however, I like it. Another, of course,
workaround is to have the date as YYYY-MM-DD in one column and the date
names as SU, MO, ... in the next, copy and paste them as unformatted
text in the Writer, replace the tabs with spaces, copy and paste them in
the Calc. But of course with this system you have to repeat the process
if you want to add new dates, which is not too bad if you have to do it
once a year.
Still another way would be to keep the 2 columns side by side but hide
the in-between vertical line. This is easy when you print, but I don't
think you can have this visual effect in the Calc itself...
emf
--
Date Calculator with all-purpose JS code
https://files.nyu.edu/emf202/public/js/datecalc.html
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org
