Dates not reading correctly

**TIERNAN** · 10-29-2012, 02:32 PM

I have the date 31/01/12 in cell A3 with the format mmm yy (Jan 12). In cell A3 I have the formula Text(A1,"yyyy.mm") 2012.01,and this works ok. The problem arises when I send the file to my Swedish colleague who opens the file and the format is not recognised. He needs to change the "yyyy.mm" to åååå.mm. Is there any way that the date format will work in both?

**:) Sixthsense :)** · 10-29-2012, 02:47 PM

Because your Windows Date setting is MM/DD/YY and your Swedish Colleague Date Setting is DD/MM/YY.

Control Panel>>Regional and Language Option>>Regional Option>>Customize>>Date>>Short Date Format

Hope this helps!

**:) Sixthsense :)** · 10-29-2012, 02:48 PM

Because your Windows Date setting is DD/MM/YY and your Swedish Colleague Date Setting is MM/DD/YY.

Control Panel>>Regional and Language Option>>Regional Option>>Customize>>Date>>Short Date Format

Hope this helps!

**Norie** · 10-29-2012, 02:50 PM

By using TEXT you've changed the date to text, so your colleague might have trouble formatting that.

If you need to display the date as yyyy.mm try using that as a custom format.

Then you'll keep the date and hopefully your colleague will be able to format it as required.

**TIERNAN** · 11-04-2012, 05:52 AM

Thank you for your advice. I have tried everything that was suggested but unfortunately it didn't work. I need the text formula (yyyy.mm) as I am using the indirect function to reference to a worksheet.

**oeldere** · 11-04-2012, 06:06 AM

If you use it ofter, you could write a macro to:

change the dataformat of a column in a certain format (from English into Swedish)

and then make text of it (if neccesary)>