Is it possible to create your own custom format?

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!

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


"Scott Steiner" wrote:

> 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!
>

Ron Coderre wrote:
>
> 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?

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.

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


"Scott Steiner" wrote:

> Ron Coderre wrote:
> >
> > 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?
>
> 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.
>

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.

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


"Scott Steiner" wrote:

> 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!
>