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
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
