Forum Discussion
Custom format for cell not working properly
I have set cell C3 to custom format mm.yy;;; but when i enter the date '02.24' it changes the cell to '01.00'. It is working for other cells on this sheet.
From what I have read, this may be excel thinking mm is minute however this should only happen if a h is present before the m. In my case there is no h and yet it will not format my cell to be the custom date format that I need (mm/yy).
My language is appropriately set to English - UK.
Hi emmadgncs,
the problem is that the custom format mm.yy;;; is not unambiguous. It could be interpreted as either a date format or a number format, depending on the context. In your case, Excel is interpreting it as a number format.
To fix this, you need to add a [Date] prefix to the custom format. This tells Excel that the format should be interpreted as a date format. The updated custom format would be [Date]mm.yy;;;.
To apply the updated custom format, follow these steps:
- Select cell C3.
- On the Home tab, in the Number group, click the Format dropdown menu.
- Select Custom.
- In the Type box, enter the following custom format:
[Date]mm.yy;;;- Click OK.
Now, when you enter the date 02.24 into cell C3, it will be formatted correctly as 02.24.
Here are some links you can use:
- Microsoft Support: Create a custom number format: https://support.microsoft.com/en-gb/office/create-and-apply-a-custom-number-format-6c308025-35da-4047-9481-c146a12063df
- Contextures: Excel dates won't change format: https://answers.microsoft.com/en-us/msoffice/forum/all/formatting-settings-disabled-in-excel-online/e6e6858f-5979-42bd-aa23-72a74f494496
- Chandoo: Custom date formats in Excel: https://support.microsoft.com/en-au/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
(LinkedIn)- Thank you, but it didn't solve the issue.
When I added [Date], excel would not allow me to hit ok and the pop up reads 'Microsoft Excel cannot use the number format you typed. Try using one of the built-in number formats.'
