Entering date format as dd/mm and reverts to dd-mmm format how do I stop this?

[Diana Allen]

I require the date format in a column to show dd/mm but every time I save and reopen the document it auto corrects it to dd/mm.  Ive tried formatting the cells to text but it replaces it with irrelevant numbers.  Online suggestions have said to change the date default in control panel but this is not working.  Would appreciate some guidance please.  thanks

[#2]

Hi Diana

Please take the below info into account. 

Dates in the Excel file must be compatible with the date format in the operating system, if the original format of the dates is different from the local date format, problems with dates will appear, they may be treated as text, or they may remain formatted as dates, but not as you think it.

Examples:

Local Date is: (M/d/yyyy)

Excel Date is: 15/12/2017

This date will be treated as text because there is no month 15!

Local Date is: (M/d/yyyy)

Excel Date is: 1/12/2017

This date will still formatted as Date in Excel, but not the date you may want.

You may think it 1/Dec/2017, but actually, it 12/Jan/2017 as the local date format!

Please make sure that the format of the dates in the Excel sheet is compatible with your local date format.

Also, I don't recommend you to change the local date format, but no problem if you change the Excel date format, but this format isn't original!

It's just a mask for the original date format which is should be always identical with the local date format.

To enter a correct date in Excel, you have to follow the local date format or enter it in the universal method (yyyy/mm/dd).

Examples:

Local Date is: (M/d/yyyy)

Enter the excel date as (12/15/2018) or (12/15) or (2018/12/15)

Local Date is: (dd/MM/yyyy)

Enter the excel date as (15/12/2018) or (15/12) or (2018/12/15)

NOTE: You can omit the year if you year you want is the current year!

After you enter the date correctly, you change its format as you want.

Just select the dates, and then click Ctrl+1 to open the Format Cells dialog box.

From the Format Cells dialog box you can go to Date category and select an existing format, or you can go to Custom to customize the date format.

For example, copy this format (DD/MM) into the Type box, and then hit OK.

Hope that helps

Haytham

[#3]

I have been using spreadsheets since before Excel was born, starting back in 1984. I get paid to produce workbooks full of complex formulae and VBA code. My point - I am not a newbie to Excel. This is something I have not seen before and have run out of ideas to sort it out.

I have a set of fields that will be updated programmatically (via VBA) with the date that an entry in that column was last changed. The date will be used in a PDF report in the footer. I can change the date to the format that I require for the report by VBA. However, because my workbooks are used in many locations, I require the date in the spreadsheet to be in the format "d mmmm yyyy" to avoid any confusion. I have formatted the cells with this custom setting, but when I enter a date as "1 Mar 2019", which normally (in all my other spreadsheets) gets reformatted to "1 March 2019", this spreadsheet is formatting it as "03/01/2019" and displaying it in the spreadsheet in that format. In the UK this is 3rd January, not 1st March. Thinking this was something to do with the difference between UK date and US date formats, I manually changed the entry to "01/03/2019" and it changed it back again. However, when I put the date in as "03/01/2019" the spreadsheet changed it to "01/03/2019", which is what I want, but I have no confidence what that date means. The spreadsheet toggles whatever date I put into the opposite format, transposing the "dd" and "mm" figures. As this information will be produced programmatically in the future and I won't be around to supervise these entries, I have no confidence in this spreadsheet producing the correct result in the future. I have tried many, many things to sort this and come to you as a last resort. My next step is to decide that this spreadsheet is corrupted and to shred it, burn the shreddings, scatter the ashes and dance on its grave, laughing (the pressure is getting to me a bit!).

[#4]

Hi @AndyGalloway,

I suggest you check the VBA code.

It may have a bug that is responsible for that.

[#5]

I have the same problem, and I'm not using any VBA code. My system date is formatted as dd/mm/yyyy. I manually changed the format of some Excel cells to d/m/yyyy h:mm. And when I enter, for example, 15/12/2021 21:00 in a cell using that custom format, Excel treats the cell as text. If I enter the date as 12/15/2021 21:00, Excel understands this as a date and displays that date and time as 15/12/2021 21:00 on the spreadsheet, reversing the date format I entered.