ORIENTLORE CHINESE CALENDAR CONVERTER: EXCEL LOOKUP REFERENCE

File: chinese-calendar-conversion-1900-2100.csv
Calendar range: lunar years 1900–2100 inclusive.
Gregorian range: 1900-01-31 through 2101-01-28 inclusive.
A row represents one civil date. This file does not convert birth times,
time zones, Julian dates, auspicious dates or complete birth charts.

IMPORT INTO EXCEL
1. Download the CSV. Use Data > From Text/CSV and comma as delimiter.
2. Set gregorian_iso and lunar_key to Text BEFORE loading. Keep the six
   original column names. In Power Query, remove automatic Changed Type
   if needed, then set these two columns to Text. Do not first convert
   them to dates and back: regional interpretation can lose information.
3. Load as an Excel table, select it and set Table Name to Calendar.
4. Create a separate worksheet for your lookup inputs. Save as .xlsx to
   preserve the table, formulas and Text input formatting.

COLUMNS
A gregorian_iso: Gregorian date key as ISO text YYYY-MM-DD.
B lunar_year: full four-digit lunar year, a number.
C lunar_month: positive month number 1–12, a number.
D lunar_day: day number 1–29 or 1–30, according to the chosen month.
E month_kind: Regular or Leap, text. Leap repeats the month number.
F lunar_key: YYYY-MM-DD-Regular or YYYY-MM-DD-Leap, text.

GREGORIAN TO LUNAR (EXACT MATCH)
Format B2 as Text, then enter: 2024-02-10
In B3 enter:
=XLOOKUP(B2,Calendar[gregorian_iso],Calendar[lunar_key],"Date not found",0)
Expected result: 2024-01-01-Regular

LUNAR TO GREGORIAN (EXACT MATCH)
Format B5 as Text, then enter: 2023-02-01-Leap
In B6 enter:
=XLOOKUP(B5,Calendar[lunar_key],Calendar[gregorian_iso],"Lunar date not found",0)
Expected result: 2023-03-22
Change B5 to 2023-02-01-Regular: result 2023-02-20.
Leap months are separate records. Never omit Regular/Leap from the key.

BUILD A LUNAR KEY FROM SEPARATE INPUTS (OPTIONAL)
Use D2 for a whole-number lunar year, E2 for a whole-number month,
F2 for a whole-number day, and G2 for Regular or Leap.
Apply Data Validation: Whole number 1900–2100 to D2, 1–12 to E2,
1–30 to F2; List with entries Regular,Leap to G2.
In H2 enter:
=IF(OR(D2="",E2="",F2="",G2=""),"",TEXT(D2,"0000")&"-"&TEXT(E2,"00")&"-"&TEXT(F2,"00")&"-"&G2)
In I2 enter:
=IF(H2="","Enter all four fields",XLOOKUP(H2,Calendar[lunar_key],Calendar[gregorian_iso],"Lunar date not found",0))
Validation restricts input shape; the exact lookup rejects an impossible
month/day combination, including a leap month absent from that year.
Example D2=2023, E2=2, F2=1, G2=Leap returns 2023-03-22.

OLDER EXCEL (2016/2019)
XLOOKUP is unavailable in these versions. With the same table and inputs:
=IFERROR(INDEX(Calendar[lunar_key],MATCH(B2,Calendar[gregorian_iso],0)),"Date not found")
=IFERROR(INDEX(Calendar[gregorian_iso],MATCH(B5,Calendar[lunar_key],0)),"Lunar date not found")
The zero in MATCH is required. Never use approximate matching for dates.

CHECKS AND RECOVERY
- Year must have four digits; month and day in keys must have two.
- Use hyphens and the exact words Regular or Leap, with no extra spaces.
- Blank, malformed and unsupported keys return the missing-date message.
- 2024-02-01-Leap is absent: lunar year 2024 has no leap second month.
- 2023-02-30-Leap is absent: that leap month has only 29 days.
- 2024-02-09 returns 2023-12-30-Regular; 2024-02-10 returns
  2024-01-01-Regular. January 1 is not the lunar-year boundary.
- Gregorian dates before 1900-01-31 or after 2101-01-28 are absent.
- If an imported ISO key becomes an Excel date serial, reimport as Text.
  Use text keys for both sides to avoid the 1900/1904 date-system issue
  and Excel's fictitious February 29, 1900. Do not use serial arithmetic
  to infer lunar months or assume every lunar month has thirty days.
- In regional Excel installations, replace formula commas with the
  local argument separator (often a semicolon).
- A CSV cannot store tables, formulas, validation or cell formatting.
  These instructions create the workbook workflow after import.

METHOD AND SOURCES
Generated from OrientLore's existing lunar-javascript calendar engine.
The generator uses the engine's actual regular/leap month list and month
lengths, and emits consecutive Gregorian dates with unique lunar keys.
Rebuild: node scripts/generate-excel-calendar-reference.mjs
Freshness check: node scripts/generate-excel-calendar-reference.mjs --check
Tests compare both converter directions at every month start and end,
plus New Year and leap-month examples and all-row key/date continuity.
This is calculated data, not a copy of an official Observatory dataset.

Hong Kong Observatory, Gregorian-Lunar Calendar Conversion Table:
https://www.hko.gov.hk/en/gts/time/conversion.htm
Official comparison tables cover 1901–2100. The 1900 portion and the
January 2101 tail are outside that official index. HKO warns that future
moon/solar-term events near midnight can cause a one-day discrepancy;
its listed future new-moon cases include 2057, 2089 and 2097. Engine
roundtrips demonstrate internal consistency, not independent verification
of every future date. Check an authoritative calendar for consequential use.

Microsoft, XLOOKUP:
https://support.microsoft.com/en-us/excel/functions/xlookup-function
Microsoft, Date systems in Excel:
https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487

Prepared: 2026-10-27. Human editorial review remains pending.