Is it possible to create your own custom format?

S

Scott Steiner

Hi,

I have a column that represents the weekday (number from 1-7) of a date
by using the worksheet weekday function. I used the custom format "dddd"
on that column to display the values as days (Sunday, Monday, ....)
rather than numbers.

Question: I want to display the days in a different language without
having to change the regional code of the PC. Is it possible to create
my own custom format that changes the way the column is displayed
_without_ changing the underlying value which is a number from 1-7?

I tried writing my own weekday function which displays fine but the
problem is that it also changes the underlying value of the cells which
I don't want to do as this affects other calculations.

Thanks for any help on how to tackle this problem!
 
R

Ron Coderre

Would something like this work for you?:

First, create a lookup table for the days:

Example (using French):
D1: Mon
E1: Lundi
D2: Tue
E2: Mardi
(etc)

For a date in A2:
B2: =VLOOKUP(TEXT(A2,"ddd"),D1:E7,2,0)

Does that help?

***********
Regards,
Ron
 
S

Scott Steiner

Ron said:
Would something like this work for you?:

First, create a lookup table for the days:

Example (using French):
D1: Mon
E1: Lundi
D2: Tue
E2: Mardi
(etc)

For a date in A2:
B2: =VLOOKUP(TEXT(A2,"ddd"),D1:E7,2,0)

Does that help?
Click to expand...

Unless I'm doing something wrong here, using vlookup is also changing
the underlying value of the cell.

At the moment the table looks like this:

- column A has dates stored
- column B is simply =WEEKDAY(A) using custom format "dddd" i.e. column
has numbers from 1-7 stored but these are represented as days without
changing the underlying value.

Using VLOOKUP is changing the underlying value of the cell i.e. not only
is "Sunday" displayed but "Sunday" is also actually the value of the
cell, something I want to avoid.
 
R

Ron Coderre

Try this:

Since Column B already has the day number

Example (using english)
C2: =CHOOSE(B2,"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

Does that help?

***********
Regards,
Ron
 
M

MrShorty

There have been several occasions where I've wanted to add my own custom
number format code. To date, I haven't found a way. If what you want
to do isn't somehow supported by Excel's built in custom number format
codes, then you have to do something like Ron is suggesting: One cell
to hold the value for future calculations and one cell in the table
that holds the "displayed" value.
 
G

Gary''s Student

1. Select an un-used cell and enter 1 into it.
2. Pull-down:
format > cells > Number > Custom and enter "mun" in place of general

3. The cell will show mun, but the formula bar will show it is still a 1 and
will function arithmetically as a 1.

Repeat this for 2-7. Then either manually thru copy/paste/special format or
via a macro apply this format to any cells containing number-days you want
displayed in your language of choise.
_____________________________________________________
Gary's Student
 
Top