如何在Google Sheets中将公历日期转换为中国农历日期

Excel中可通过带区域参数的TEXT函数直接实现公历转农历，公式为：
=TEXT(A2; "[$-130000]dd.mm.yy")
Excel端效果参考：

上述公式在Google Sheets中无法生效，原因是Google Sheets的TEXT函数不支持Windows系统专属的区域语言代码参数，没有内置农历格式转换能力，可通过以下两种方式实现需求：

方案1：自定义Apps Script函数（推荐，稳定性高） #

该方案无需依赖外部资源，支持1900-2100年范围的日期转换：

1. 打开目标Google Sheets，点击顶部菜单栏「扩展程序」→「Apps 脚本」
2. 在弹出的脚本编辑器中粘贴以下代码，点击保存按钮，按提示完成账号授权

function LUNAR(inputDate) {
  const dateVal = inputDate instanceof Date ? inputDate : new Date(inputDate);
  const lunarCalendarData = [0x04bd8,0x04ae0,0x0a570,0x054d5,0x0d260,0x0d950,0x16554,0x056a0,0x09ad0,0x055d2,0x04ae0,0x0a5b6,0x0a4d0,0x0d250,0x1d255,0x0b540,0x0d6a0,0x0ada2,0x095b0,0x14977,0x04970,0x0a4b0,0x0b4b5,0x06a50,0x06d40,0x1ab54,0x02b60,0x09570,0x052f2,0x04970,0x06566,0x0d4a0,0x0ea50,0x06e95,0x05ad0,0x02b60,0x186e3,0x092e0,0x1c8d7,0x0c950,0x0d4a0,0x1d8a6,0x0b550,0x056a0,0x1a5b4,0x025d0,0x092d0,0x0d2b2,0x0a950,0x0b557,0x06ca0,0x0b550,0x15355,0x04da0,0x0a5b0,0x14573,0x052b0,0x0a9a8,0x0e950,0x06aa0,0x0aea6,0x0ab50,0x04b60,0x0aae4,0x0a570,0x05260,0x0f263,0x0d950,0x05b57,0x056a0,0x096d0,0x04dd5,0x04ad0,0x0a4d0,0x0d4d4,0x0d250,0x0d558,0x0b540,0x0b6a0,0x195a6,0x095b0,0x049b0,0x0a974,0x0a4b0,0x0b27a,0x06a50,0x06d40,0x0af46,0x0ab60,0x09570,0x04af5,0x04970,0x064b0,0x074a3,0x0ea50,0x06b58,0x055c0,0x0ab60,0x096d5,0x092e0,0x0c960,0x0d954,0x0d4a0,0x0da50,0x07552,0x056a0,0x0abb7,0x025d0,0x092d0,0x0cab5,0x0a950,0x0b4a0,0x0baa4,0x0ad50,0x055d9,0x04ba0,0x0a5b0,0x15176,0x052b0,0x0a930,0x07954,0x06aa0,0x0ad50,0x05b52,0x04b60,0x0a6e6,0x0a4e0,0x0d260,0x0ea65,0x0d530,0x05aa0,0x076a3,0x096d0,0x04afb,0x04ad0,0x0a4d0,0x1d0b6,0x0d250,0x0d520,0x0dd45,0x0b5a0,0x056d0,0x055b2,0x049b0,0x0a577,0x0a4b0,0x0aa50,0x1b255,0x06d20,0x0ada0];
  const monthNames = ["正","二","三","四","五","六","七","八","九","十","冬","腊"];
  const dayNames = ["初一","初二","初三","初四","初五","初六","初七","初八","初九","初十","十一","十二","十三","十四","十五","十六","十七","十八","十九","二十","廿一","廿二","廿三","廿四","廿五","廿六","廿七","廿八","廿九","三十"];
  
  const baseDate = new Date(1900, 0, 31);
  let dayOffset = Math.floor((dateVal - baseDate) / 86400000);
  let lunarYear = 1900, yearDays = 0;
  for (; lunarYear < 2100 && dayOffset > 0; lunarYear++) {
    yearDays = getLunarYearTotalDays(lunarYear);
    dayOffset -= yearDays;
  }
  if (dayOffset < 0) { dayOffset += yearDays; lunarYear--; }
  
  const leapMonthVal = getLeapMonth(lunarYear);
  let isLeapMonth = false, lunarMonth = 1, monthDaysVal = 0;
  for (; lunarMonth < 13 && dayOffset > 0; lunarMonth++) {
    if (leapMonthVal > 0 && lunarMonth === leapMonthVal + 1 && !isLeapMonth) {
      lunarMonth--;
      isLeapMonth = true;
      monthDaysVal = getLeapMonthDays(lunarYear);
    } else {
      monthDaysVal = getLunarMonthDays(lunarYear, lunarMonth);
    }
    if (isLeapMonth && lunarMonth === leapMonthVal + 1) isLeapMonth = false;
    dayOffset -= monthDaysVal;
  }
  if (dayOffset === 0 && leapMonthVal > 0 && lunarMonth === leapMonthVal + 1) {
    isLeapMonth = !isLeapMonth;
    if (!isLeapMonth) lunarMonth--;
  }
  if (dayOffset < 0) { dayOffset += monthDaysVal; lunarMonth--; }
  const lunarDay = dayOffset + 1;
  
  function getLeapMonth(y) { return lunarCalendarData[y - 1900] & 0xf; }
  function getLeapMonthDays(y) { return getLeapMonth(y) ? ((lunarCalendarData[y - 1900] & 0x10000) ? 30 : 29) : 0; }
  function getLunarMonthDays(y, m) { return (lunarCalendarData[y - 1900] & (0x10000 >> m)) ? 30 : 29; }
  function getLunarYearTotalDays(y) {
    let sum = 348;
    for (let i = 0x8000; i > 0x8; i >>= 1) sum += (lunarCalendarData[y - 1900] & i) ? 1 : 0;
    return sum + getLeapMonthDays(y);
  }
  
  return `${isLeapMonth ? "闰" : ""}${monthNames[lunarMonth - 1]}月${dayNames[lunarDay - 1]}`;
}

3. 回到表格界面，直接在单元格中输入公式=LUNAR(A2)即可返回A2单元格公历日期对应的农历结果。

方案2：公开数据集匹配（无需写代码） #

如果不想配置脚本，可以通过IMPORTRANGE函数引入公开的公历-农历映射表，再用VLOOKUP匹配结果，缺点是依赖第三方数据源，稳定性较差。
公式示例：
=VLOOKUP(TO_TEXT(A2), IMPORTRANGE("对应公开农历表ID", "日期映射!A:B"), 2, FALSE)
使用前需要先给表格授予访问对应数据源的权限，若数据源失效转换结果会报错。

内容的提问来源于stack exchange，提问作者stereos