Entering Custom Date Format in Excel

Copper Contributor

Up until recently I was able to enter a date as 8.17 and it would default to 08/17/23 as I has specified in the custom format.  All of a sudden, a date entered as 8.17 defaults to 1/8/00.  What changed?  There is a post on the internet that says you can customise the date value from slashes to periods through the regional specification pulldowns for dates but that's gone now as well.  How do I get this to work again?

1 Reply


It sounds like the date format settings on your Excel for Mac might have been inadvertently changed. To resolve this issue and get the desired custom date format working again, you can follow these steps:

  1. Check Date Format Settings:
    • Click on the Apple menu () in the top-left corner of your screen.
    • Select "System Preferences."
    • Choose "Language & Region" (or "Region & Language" depending on your macOS version).
    • Click on the "Advanced" button in the bottom-right corner.
    • Ensure that the "Dates" tab is selected.
  2. Customize Date Format:
    • In the "Dates" tab, you should see options to customize the short date, medium date, and long date formats.
    • Make sure that the short date format is set to "MM/dd/yy" to match your desired custom format.
    • If the format has changed to "dd/MM/yy" or any other format, modify it to "MM/dd/yy."
  3. Check Excel Format:
    • Open Excel and select the cell where you want to enter the date (e.g., A1).
    • In the formula bar, enter the date as "8.17" and press Enter.
    • Right-click on the cell (A1) and choose "Format Cells."
    • In the "Format Cells" dialog, go to the "Number" tab and select "Date" from the list on the left.
    • Choose the "Type" as "03/14/01" (or any other date format that matches your desired custom format).
    • Click "OK."
  4. Test the Entry:
    • Enter "8.17" into the cell (A1) again and see if it now formats correctly as "08/17/23."

If the above steps don't resolve the issue, it's possible that there might be other settings or factors affecting the date format behavior. In that case, you might want to consider restarting your computer and Excel, or you could also reach out to Microsoft support for further assistance.

Remember that Excel's behavior might vary based on the macOS version you are using and any updates that have been applied. The text and the steps were created with the help of AI.


My answers are voluntary and without guarantee!


Hope this will help you.