EXCEL 公历转农历

南岗吉厦李

日期        农历                                 2018-1-5        #NAME?                        1645        5                                 1648        4                                 1651        1                                 1653        6                                 1656        5                                 1659        3                                 1661        8                                 1664        6                                 1667        4

醇爷们

2023年5月19日~6月17日转换的农历都错开一天，其余的年份没有发现错误。

高个子

个人认为像丁酉年这种的是可以找得到规律的，但是月份不存在规律，这种问题最佳方案是网抓

XCMqxl

农历如何转阳历vba代码，求高手

han_ice

测试发现，2024/1/11—2024/2/9 月份错误，请问怎么修正，谢谢。

hjp3798

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)

hjp3798

=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)

hjp3798

=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)