Date format in Switzerland...

[Crazy_Ivan]

Hi there,

Strange one - a colleague in Switzerland would like to be able to enter dates in the format 2.2.2023
Previously he has been able to do this and Excel has recognized this as a date
He got a new PC and when he enters in this way, Excel recognizes this as text, not a date

This is not a format within Excel question, because the cells he is updating are actually formatted dd mmm yyyy

So I guess there is a setting in Excel that recognizes the European format of dates entered, i.e. with the periods rather than the /s

Everything worked fine on his old PC, but we can't figure out what should be a basic question!

(We have tried changing Language options, but no luck so far)

Any advice would be hugely appreciated!!

 

[Joe4]

See here: Excel International Formats | CustomGuide

 

[awoohaw]

I can't tell how you have determined the formatting in those cells formatted as "dd mmm yyyy" .... have you selected those cells and removed all formatting (changing the format to general) and confirmed that the cell now displays a date serial number ?

 

[Crazy_Ivan]

I can't tell how you have determined the formatting in those cells formatted as "dd mmm yyyy" .... have you selected those cells and removed all formatting (changing the format to general) and confirmed that the cell now displays a date serial number ?

Thanks for your comment

This is a shared workbook

So it is not an issue with formating the cells

It relates to how Excel recognises inputs

So if he enters 3.1.23, Excel does not recognize this as a date
He needs to enter 3/1/23

Previously, he could enter 3.1.23 and Excel would recognize this as a date

 

[Joe4]

Thanks for your comment

This is a shared workbook

So it is not an issue with formating the cells

It relates to how Excel recognises inputs

So if he enters 3.1.23, Excel does not recognize this as a date
He needs to enter 3/1/23

Previously, he could enter 3.1.23 and Excel would recognize this as a date




View attachment 98647

I disagree. Look at item 4 in that link I gave you. That shows how Excel expects you to input dates, and you can change those settings.

The only other way I can think of is to use VBA to "intercept" dates inputs into certain cells, and have the VBA code convert them into valid dates.

 

[awoohaw]

Thanks for your comment

This is a shared workbook

So it is not an issue with formating the cells

It relates to how Excel recognises inputs

So if he enters 3.1.23, Excel does not recognize this as a date
He needs to enter 3/1/23

Previously, he could enter 3.1.23 and Excel would recognize this as a date




View attachment 98647

@Crazy_Ivan , if you want the forum to help you best, at least try to help the forum help you. The amount of time it took you to write that reply was ten times the amount of time it takes to select cells, click number format, click general.

I am not saying you are wrong, but you are not helping the forum help you find a solution.

 

[Leo Skywalker]

This problem is not related to Excel, but to the Windows Operating System.

You can define a custom time format in Windows through the Region tab in the Control Panel.

First, hit the Win key, type “Control Panel”, and select the appropriate option to launch Control Panel.
Next, under Clock and Region, click change date, time, or number formats to open the Region panel.
Once in the Region tab, hit Additional settings - Date - short date
Set it to: d.M.yy

Go back to Excel and enter the dates, Excel will recognize it.

 

[Joe4]

This problem is not related to Excel, but to the Windows Operating System.

You can define a custom time format in Windows through the Region tab in the Control Panel.

First, hit the Win key, type “Control Panel”, and select the appropriate option to launch Control Panel.
Next, under Clock and Region, click change date, time, or number formats to open the Region panel.
Once in the Region tab, hit Additional settings - Date - short date
Set it to: d.M.yy

Go back to Excel and enter the dates, Excel will recognize it.

Yep, I already mentioned that in my first reply.