Excel:Date and Time in A language not defined in Control Panel 2

[IJR]

Unless I am wrong(as usual huh huh), excel allows one to change date formats but not the language. I mean the 3rd of march can be 08/03 or 3Mar or March 3 etc but not 3Mart as would be in a different language. Apparently it refers to whatever Regional settings are there in The Control Panel.

Does anyone want to tell us how I can fool excel by formula or code to switch between languages(only as related to dates)? I sometimes need this utility.

Regs
IJR

 

[Mala]

IJR, you should get a reward for thinking up the weirdest requests. This is a tough one...

 

[cryoguy]

The following formula will translate a date to German.

=IF(C1=0,"",CHOOSE(MONTH(C1),"Januar", "Februar","Marz","April", "Mai","Juni","Juli", "August","September","Oktober","November","Dezember&quot&" " &DAY(C1)&", "&YEAR(C1))

The original date of course is in cell "C1". The "IF" formula prevents the formula from printing Januar 1, 1900 if the cell "C1" is empty.

The only drawback is that these long formulas can be annoying. Perhaps someone can provide a macro. On the plus side you could hide column "C" for printing purposes but still use it to sort your data by date.

 

[Mala]

Yes, using a formula/user-defined function in another cell is the ONLY way to do this. In this case it's just not possible to format the original date cell as per requirement.

INTERESTING TIP:
Now for cryoguy's observation about annoyance at repeatedly entering formulas:
The tedium CAN be avoided if u use named formulas - if the formula cell is at a fixed relative position with respect to the source cell.

Let's say the formula cell will ALWAYS be the adjacent RIGHT cell of the source cell

To do this follow this procedure:
1.In 'C1' put the date

2.Select 'D1'

3. Do Insert>Name>Create

4.In the Name box type: GermanDtFormat (or a shorter name if you wish)

5.In the refers to box type (I presume the formula is correct&quot:
=IF(!C1=0,"",CHOOSE(MONTH(!C1),"Januar", "Februar","Marz","April", "Mai","Juni","Juli", "August","September","Oktober","November","Dezember&quot&" " &DAY(!C1)&", "&YEAR(!C1))

6. Click 'Add' and close the dialog box

Note the '!' mark preceding the cell address - it has the special significance that it'll work with ANY sheet in the workbook.

Test this out:
Switch to another sheet
In range 'Y5' put a date
In Range 'Z5' enter the formula '=GermanDtFormat'
It'll work!!

Good luck!

 

[IJR]

Thanx Mala once more and again. I never knew that a name could be associated to a formula.

regs
IJR

 

[Mala]

IJR: Here's something even more versatile

Using a user defined function in place of a named formula is a more flexible solution in that there is no restriction on the location of the formula cell with reference to the source cell.

My Regional Setting for date is dd-mm-yyyy.

In a VBA module type the following code:

Public Function DateGerman(Ref) As String

If IsDate(Ref) Then

Mo = Month(Ref)

MoName = WorksheetFunction.Choose(Mo, "Januar", "Februar","Marz","April", "Mai","Juni","Juli", "August","September","Oktober","November","Dezember&quot

DateGerman = Format(Day(Ref), "00 &quot + MoName + " " + Format(Year(Ref), "0&quot

End If

End Function

Note that we use the VBA function IsDate to check if Ref is a valid date - if not, the function exits with a "" (null) value. Also that the Day(), Month(), Year() functions here are VBA functions - so the 'WorksheetFunction.' prefix is not used.

With this function in place, you are freed of restrictions of both location and type of source date. Ref can be either a cell reference containing a date OR a date itself OR a even a string that looks like a date.

All of the following will return the same result:

=DateGerman(B2) - if cell B2 contains the date 12/3/56
=DateGerman("12/3/56&quot
=DateGerman("12 Mar 56&quot

Really prolific, isn't it?

If you put this code in Personal.xls (which resides in XLStart and opens every time Excel is used), you can use the function in ANY open workbook and it'll work.