有小伙伴在后台留言询问,公历日期如何转为农历日期。
比如,下面的Excel表格,将B列的日期,转成对应的农历。
C2单元格输入公式:=TEXT(B2,"[$-130000]yyyy-m-d"),下拉即可。除了上面的公式写法,再给大家发几个不同的格式,对应的函数公式肯定也是不一样的哈。可以收藏备用。
=IF(--TEXT(B2,"[$-130000]d")<11,TEXT(B2,"[$-130000][dbnum1]m月初d"),SUBSTITUTE(TEXT(B2,"[$-130000][dbnum1]m月d"),IF(--TEXT(B2,"[$-130000]d")>20,"二十",""),"廿"))=TEXT(B2,"[$-130000]yyyy年m月"&IF(LEN(--TEXT(B2,"[$-130000]dd"))=1,"初","")&"d")=TEXT(B2," [$-130000][DBNum1]m月d日")=MID(" 甲乙丙丁戊己庚辛壬癸",MOD(TEXT(B2,"[$-130000]e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉 戌亥",MOD(TEXT(B2,"[$-130000]e")-4,12)+1,1)&"年"&IF(TEXT(B2," [$-130000]m")*1=12," 腊",IF(TEXT(B2," [$-130000]m")*1=1," 正",TEXT(B2," [$-130000][DBNum1]m")))&"月"&IF(TEXT(B2," [$-130000]d")-9<=1,"初",IF(TEXT(B2," [$-130000]d")-29>=1,"卅",IF(TEXT(B2," [$-130000]d")-19>=1,"廿","十")))&IF(RIGHT(TEXT(B2," [$-130000]d"),1)*1=0,"",TEXT(RIGHT(TEXT(B2," [$-130000]d"),1)*1,"[DBNum1]d"))&"日"=IF(TEXT(B2," [$-130000]m")*1=12," 腊",IF(TEXT(B2," [$-130000]m")*1=1," 正",TEXT(B2," [$-130000][DBNum1]m")))&"月"&IF(TEXT(B2," [$-130000]d")-9<=1,"初",IF(TEXT(B2," [$-130000]d")-29>=1,"卅",IF(TEXT(B2," [$-130000]d")-19>=1,"廿","十")))&IF(RIGHT(TEXT(B2," [$-130000]d"),1)*1=0,"",TEXT(RIGHT(TEXT(B2," [$-130000]d"),1)*1,"[DBNum1]d"))&"日"
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。