ROC Dates in Excel: Formulas and Pitfalls

For text like 115/07/29 in cell A1, the formula =DATE(LEFT(A1,3)+1911,MID(A1,5,2),RIGHT(A1,2)) returns a real Excel date. Excel does not recognise ROC years on its own — every column needs this conversion explicitly.

Why Excel gets this wrong by default

Excel's automatic date detection assumes a Gregorian year. Type 115/07/29 into a cell and Excel leaves it as plain text, because 115 isn't a plausible year in its parsing rules, or worse, it can misparse similar-looking strings as something else entirely depending on your regional settings. Either way, you cannot sort, filter by date range, or run date arithmetic on the raw text.

The fix is to convert explicitly with DATE(), which takes a year, month and day and always builds a Gregorian-calendar serial number regardless of how the cell is formatted:

Cell A1	115/07/29 (text)
Formula	=DATE(LEFT(A1,3)+1911,MID(A1,5,2),RIGHT(A1,2))
Result	29/07/2026 (a real date, formatted per your cell's number format)

Cross-check any single value with the ROC year converter before trusting a formula against a full spreadsheet.

Handling different separators and widths

The LEFT/MID/RIGHT formula above assumes a fixed-width format: three digits, a separator, two digits, a separator, two digits. Government exports are not always this tidy. Two common variants and their fixes:

The unpadded version is verbose because FIND has to locate each slash independently. If a column is large and irregular, it is usually faster to clean the separators first with Find & Replace or TEXTSPLIT (Excel 365) and then apply the simple fixed-width formula.

Displaying a real date in ROC format

If the underlying value is already a proper Excel date and you only need it to display as an ROC year — for a printed form, for example — use a custom number format rather than converting the value itself:

[$-404]e/m/d

404 is the hexadecimal Windows locale identifier for Taiwan Chinese; combined with the era token e it prints the year in the Republic of China calendar rather than the Gregorian one. Apply it through Format Cells → Custom. This only changes how the cell displays; the stored serial number, and any formula referencing the cell, is unaffected.

The locale trap

Changing your Windows region or Excel's language settings to Taiwan changes how existing date values are displayed using locale-aware formats, but it does not retroactively parse a column of ROC-formatted text. A spreadsheet built by a colleague on a Taiwan-locale machine can also silently use different default date formats when you open it elsewhere, which is a common source of "the dates all shifted" bug reports that are actually formatting, not data, problems. When in doubt, check whether a cell is left-aligned (text) or right-aligned (a real number/date) before trusting either the display or a formula built on top of it.

Converting a whole column at once

Put the DATE() formula in a helper column, fill it down the full range, then select the results and Paste Special → Values over the original column if you want to discard the text version. For large or irregular imports — mixed separators, some rows already real dates, some rows blank — Power Query's Date.From with a small custom M column handles the branching more reliably than nested IF formulas, and is easier to audit later.

Frequently asked questions

What is the Excel formula to convert a ROC date to a real date?

For text like 115/07/29 in cell A1: =DATE(LEFT(A1,3)+1911,MID(A1,5,2),RIGHT(A1,2)). This returns a real Excel date serial you can format, sort and calculate with.

Why does my ROC date column show as text instead of a date?

Because Excel's automatic date parsing does not recognise a three-digit ROC year as a year at all, so it leaves the cell as plain text, left-aligned. You need an explicit formula, such as DATE() with LEFT/MID/RIGHT, to turn it into a real date value.

Can I just change my Windows region to Taiwan and have Excel read ROC dates?

Only for display, and only for cells already stored as real dates. Setting the Windows or Excel locale to Chinese (Taiwan) changes how an existing date value is displayed, using the built-in Taiwan calendar — it does not retroactively parse a text column of ROC-formatted strings for you.

How do I display a real Excel date in ROC format?

Use a custom number format such as [$-404]e/m/d, where 404 is the hexadecimal Windows locale ID for Taiwan Chinese, which formats the year in the ROC calendar. This only affects display; the underlying serial number is unchanged.

How do I convert a whole column of ROC dates at once?

Write the DATE()/LEFT()/MID()/RIGHT() formula in one helper column, fill it down, then paste the results as values over the original column if you no longer need the text version. Power Query's Date.From with a custom M step is a more robust option for very large or irregular imports.