Nov 02 2022 07:20 AM
Nov 02 2022 07:20 AM
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
Nov 06 2022 02:40 AM
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
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.
Nov 07 2022 06:41 AM
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.