A date with a two-digit year lands in the wrong century for one of two reasons. Excel's own cutoff treats years 00 to 29 as 20xx and 30 to 99 as 19xx, and a formula such as DATE(24,1,5) adds 1900 and gives 1924. Both are fixed by choosing the century yourself.
Why does 24 become 1924?
There are two separate mechanisms, and it helps to know which one you hit.
- Typed or converted text. When Excel turns a text such as 05/01/24 into a date, it applies the two-digit year rule from Windows settings. By default that interprets 00 to 29 as the 2000s and 30 to 99 as the 1900s. So 24 is 2024, but 31 is 1931.
- The DATE function. DATE treats a year between 0 and 1899 as an offset from 1900. DATE(24,1,5) is therefore 5 January 1924. This is the one that bites when a formula cuts the last two characters off the text.
Where does it show up in statements?
Statements with dd/mm/yy dates are the main source. Most transaction dates are recent and come out right, so the problem hides. It appears when a date is far ahead, such as the maturity date of a long deposit, a loan end date or a policy renewal, shown as a two-digit year past the cutoff. It also appears when you build dates with a formula using RIGHT(A2,2). Say a loan end date reads 15/03/31, the default rule reads it as 15 March 1931.
Fix 1: force the century in the formula
For text such as 05/01/24 in A2, add 2000 yourself:
- =DATE(2000+VALUE(RIGHT(A2,2)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2)))
This always returns 2024 for 24 and 2031 for 31. If your list could contain genuinely old dates, such as years in the 1990s, use a pivot year of your choice:
- =DATE(IF(VALUE(RIGHT(A2,2))<=40,2000,1900)+VALUE(RIGHT(A2,2)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2)))
Here 40 is an example cutoff you set. Years up to 40 are read as 20xx and the rest as 19xx. Pick the cutoff that suits your data, and say so in a note on the sheet.
Fix 2: repair dates already in the wrong century
If B2 holds 05/01/1924 and should be 2024, add a hundred years while keeping day and month:
- =IF(YEAR(B2)<2000,DATE(YEAR(B2)+100,MONTH(B2),DAY(B2)),B2)
Only apply it where every date before 2000 is certainly wrong. For a statement of this decade, that is true.
| Cell shows | Formula result | Why |
|---|---|---|
| 05/01/1924 | 05/01/2024 | Year below 2000, plus 100 |
| 18/02/2024 | 18/02/2024 | Left unchanged |
| 15/03/1931 | 15/03/2031 | Plus 100, but check it was meant as 2031 |
Fix 3: change the Windows cutoff?
Windows has a setting under the regional options, in the Additional settings of the date tab, that sets the range for two-digit years. It is possible to change, and the path differs between Windows versions, but it affects every program and every file, and it does not alter dates that are already converted. I would rather make the formula explicit than rely on a machine setting that a colleague may not share.
Text to Columns and typing a date directly follow the same Windows rule. Entering 5/1/24 gives 2024, and entering 15/3/31 gives 1931. That is why a column can look perfect for ten years of statements and then fail on one future date, such as a deposit maturity.
If a value sits beyond your cutoff on purpose, the safest course is a four-digit year in the source or a formula that states the century, so the file does not depend on any setting.
Prevent it next time
- Ask your bank for four-digit years if the download page lets you choose.
- If you use a formula, write the 2000 into it so the intention is visible.
- Add a check cell: =COUNTIF($A$2:$A$500,"<"&DATE(2000,1,1)) should be 0 for modern statements.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


