A2 为公历 在B2 处输入以下公式 就可算出农历
=MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A2,"[$-130000]e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A2,"[$-130000]e")-4,12)+1,1)&MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(TEXT(A2,"[$-130000]e")-4,12)+1,1)&"年"&IF(VALUE(TEXT(A2,"[$-130000]m"))=IFNA(VLOOKUP(VALUE(TEXT(A2,"[$-130000]yyyy")),{2001,4;2004,2;2006,7;2009,5;2012,4;2014,9;2017,6;2020,4;2023,2;2025,6;2028,5;2031,3;2033,11;2036,6;2039,5;2042,2;2044,7;2047,5;2050,3;2052,8;2055,6;2058,4;2061,3;2063,7;2066,5;2069,4;2071,8;2074,6;2077,4;2080,3;2082,7},2,0),13)+1,"闰","")&VLOOKUP(--IF(VALUE(TEXT(A2,"[$-130000]m"))>IFNA(VLOOKUP(VALUE(TEXT(A2,"[$-130000]yyyy")),{2001,4;2004,2;2006,7;2009,5;2012,4;2014,9;2017,6;2020,4;2023,2;2025,6;2028,5;2031,3;2033,11;2036,6;2039,5;2042,2;2044,7;2047,5;2050,3;2052,8;2055,6;2058,4;2061,3;2063,7;2066,5;2069,4;2071,8;2074,6;2077,4;2080,3;2082,7},2,0),13),TEXT(A2,"[$-130000]m")-1,TEXT(A2,"[$-130000]m")),{1,"正月";2,"二月";3,"三月";4,"四月";5,"五月";6,"六月";7,"七月";8,"八月";9,"九月";10,"十月";11,"冬月";12,"腊月"},2,0)&VLOOKUP(--TEXT(A2,"[$-130000]d"),{1,"初一";2,"初二";3,"初三";4,"初四";5,"初五";6,"初六";7,"初七";8,"初八";9,"初九";10,"初十";11,"十一";12,"十二";13,"十三";14,"十四";15,"十五";16,"十六";17,"十七";18,"十八";19,"十九";20,"二十";21,"廿一";22,"廿二";23,"廿三";24,"廿四";25,"廿五";26,"廿六";27,"廿七";28,"廿八";29,"廿九";30,"三十"},2,0) |