Date Format

[Ron-PawKy_BayKon]

Running Windows 11

This is a problem encountered today. Trying to format a cell: using Date, 14/03/2012, English (United Kingdom) in the dialogue box.

Today I entered into the formatted cell 27/10/22 and that is what appears in the cell, not what I expected as the formatting should have produced 27/10/2022. I expected this format as that is what is shown in other cells on the worksheet entered before today (the sample in the Format Cells dialogue box shows 27/10/22). This problem is magnified in an adjacent cell with the formula =TEXT(EL4,"ddd") (where EL4 is the date formatted cell) which should produce a three-letter day of the week but instead just shows 27/10/22.

Tried Microsoft Excel support but the best they could do is to refer me to the Community. They did run a repair to Excel but that didn't correct the problem. I've also tried the following to no effect:

1) Formatting cells elsewhere on the worksheet

2) Format cells on a new blank worksheet.

3) Close the worksheet and reboot the computer.

4) Changing the format to something else then back to my desired format.

This is most frustrating. 

Is this a bug?

Any suggestions to solve this problem would be appreciated

[#2]

@Ron-PawKy_BayKon 

Hello, it is very likely that your entry is not a date but a text. This is supported by the fact that your TEXT() function does not produce a day of the week. No matter what number (and a date is really a number in Excel) is entered, a weekday should be displayed.

Please check your entry in the cell by calling the function

=ISTEXT(EL4)

you enter. If the answer is TRUE, then it is a text that Excel cannot automatically convert into a number. This then leads to your error.

A good indication of the cell content is the formatting in the cell. If the alignment is not specified by manual input, text is always left-aligned and numbers and the date are always right-aligned.

I hope this has helped you.

[#3]

@dscheikey 

Thank you for your reply.

How crazy, changing the date format is dependent on the alignment of the entry. I had a centre alignment of merged cells and the date dialogue box sample refused to identify the date format. Following your advice I reset the alignment to the right. Immediately the cell formatted to the date format I required, I then reset the alignment to centre. Problem solved.

I wonder why the cell format dialogue box does not take the alignment into consideration when showing a sample of the appearance!

Once again many thanks for your help, much appreciated, especially for my sanity.