Excel:Date and Time in A language not defined in Control Panel
Excel:Date and Time in A language not defined in Control Panel
(OP)
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
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





RE: Excel:Date and Time in A language not defined in Control Panel
RE: Excel:Date and Time in A language not defined in Control Panel
=IF(C1=0,"",CHOOSE(MONTH(C1),"Januar", "Februar","Marz","April", "Mai","Juni","Juli", "August","September","Oktober","November","Dezember")&" " &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.
RE: Excel:Date and Time in A language not defined in Control Panel
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"):
=IF(!C1=0,"",CHOOSE(MONTH(!C1),"Januar", "Februar","Marz","April", "Mai","Juni","Juli", "August","September","Oktober","November","Dezember")&" " &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!
RE: Excel:Date and Time in A language not defined in Control Panel
regs
IJR
RE: Excel:Date and Time in A language not defined in Control Panel
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")
DateGerman = Format(Day(Ref), "00 ") + MoName + " " + Format(Year(Ref), "0")
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")
=DateGerman("12 Mar 56")
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.