Office for Mac Issues

How to Change the Two-Digit Year Cutoff in Excel for Mac

Aamir Naveed Akram Oct 1, 2026 868 views

Question details

The user needs to change the date options in Excel for Mac so that two-digit year entries (like '29') are interpreted in the correct century.

How to Change the Two-Digit Year Cutoff in Excel for Mac

Product: Microsoft Excel for Mac
Device & OS: Mac
Scenario: Entering dates using a two-digit year format and needing Excel to accurately map them to either the 1900s or 2000s based on a specific cutoff.
Observed behavior: Excel for Mac applies a default cutoff year to interpret two-digit dates, which may cause newly entered dates to register in the wrong century until the cutoff setting is manually adjusted.

Before you start

Before adjusting this setting, note that changing the date cutoff will not alter previously entered dates, but it will affect how all new two-digit year entries are interpreted across your Mac.

Solution 1Recommended

Modify Date Options in Excel Preferences

Adjust the built-in macOS Excel preferences to change the century cutoff rule for two-digit date entries.

By modifying the 'Edit' preferences in Excel for Mac, you can instruct the application on exactly how to read two-digit years. This is essential for historical data entry or forward-looking financial forecasting where century accuracy matters.

Open Excel Preferences

Launch Microsoft Excel on your Mac. Click on 'Excel' in the top Apple menu bar (next to the Apple logo) and select 'Preferences' from the dropdown menu.

Access Edit Settings

In the Preferences dialog box, locate the 'Authoring' section and click on the 'Edit' icon.

Adjust the Cutoff Year

Look for the 'Date Options' section. Find the setting that specifies how two-digit years are interpreted and adjust the cutoff year limit to your preferred threshold (e.g., to ensure '29' is read as 2029 rather than 1929).

Test the New Setting

Close the Preferences window to save your changes. Type a two-digit year like '29' into a new blank cell and format the cell as a full date to verify that Excel now resolves it to the correct century.

Modify Date Options in Excel Preferences

Tip: To completely avoid century ambiguity in your spreadsheets, it is highly recommended to format your cells to display four-digit years (YYYY) and enter the full four digits when typing.

Free Microsoft Office alternative

Manage Spreadsheet Dates Effortlessly with WPS Office

If Excel for Mac settings are causing date formatting headaches, try WPS Office. As a free, lightweight alternative, WPS Spreadsheet offers seamless date handling and robust compatibility with Microsoft formats, ensuring your data remains accurate without complex menu navigation.

1. Download and Install: Get WPS Office for free from the official website and complete the quick installation on your Mac.
2. Open Your Data: Launch WPS Spreadsheet and seamlessly open your existing .xlsx workbooks without losing any formatting.
3. Format Cells Promptly: Select your date columns, right-click to choose 'Format Cells', and apply a strict 4-digit date format to eliminate any future century confusion.

Fully compatible with Microsoft Excel (.xlsx, .xls) files and complex date formats.Intuitive cell formatting allows you to easily switch between two-digit and four-digit years.Lightweight software that installs quickly and runs smoothly on all macOS versions.Familiar ribbon interface that requires zero learning curve for transitioning Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel change my '29' year to 1929 instead of 2029?

By default, Microsoft Office uses a specific cutoff year to guess the century for two-digit years. Numbers from 00 to 29 are often treated as 2000-2029, while 30 to 99 are treated as 1930-1999. Depending on your OS settings or Excel version, this cutoff might differ, causing '29' to fall into the previous century.

Can I apply the two-digit year cutoff to a specific workbook only?

No, the two-digit year cutoff setting in Excel Preferences is an application-level configuration. Changing it will affect how new two-digit years are interpreted across all workbooks opened and edited on that specific Mac.

Will changing the cutoff year alter the dates I already entered?

No, adjusting the date options cutoff only affects dates entered after the change is made. Any dates previously typed into your spreadsheets will remain in the century they were originally assigned.

How can I easily convert existing incorrect two-digit dates to the right century?

If you have existing dates stuck in the wrong century, you can use the EDATE function or add/subtract 36525 days (100 years including leap years) in an adjacent column to correct them, then copy and paste the new values as text.

Office App Problems