Excel教程：公历日期转农历的6个公式 |
您所在的位置：网站首页 › 阴历的格式 › Excel教程：公历日期转农历的6个公式 |
Excel教程：公历日期转农历的6个公式
|
有小伙伴在后台留言询问，公历日期如何转为农历日期。 比如，下面的Excel表格，将B列的日期，转成对应的农历。  C2单元格输入公式：=TEXT(B2,"[$-130000]yyyy-m-d")，下拉即可。公式解释：B2：表示要转化的公历日期；yyyy-m-d：表示输入日期的格式；[$-130000]：表示公历转阴历的特殊参数。除了上面的公式写法，再给大家发几个不同的格式，对应的函数公式肯定也是不一样的哈。可以收藏备用。D列公式：=IF(--TEXT(B2,"[$-130000]d")20,"二十",""),"廿"))E列公式：=TEXT(B2,"[$-130000]yyyy年m月"&IF(LEN(--TEXT(B2,"[$-130000]dd"))=1,"初","")&"d")F列公式：=TEXT(B2," [$-130000][DBNum1]m月d日")G列公式：=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")-19>=1,"廿","十")))&IF(RIGHT(TEXT(B2," [$-130000]d"),1)*1=0,"",TEXT(RIGHT(TEXT(B2," [$-130000]d"),1)*1,"[DBNum1]d"))&"日"H列公式：=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")-19>=1,"廿","十")))&IF(RIGHT(TEXT(B2," [$-130000]d"),1)*1=0,"",TEXT(RIGHT(TEXT(B2," [$-130000]d"),1)*1,"[DBNum1]d"))&"日"
|
【本文地址】