WPS技巧:票据的出票日期中文大写转换最简的公式

来源:岁月联盟 编辑:lcx 时间:2010-03-08
【原创】
Q:求票据的出票日期中文大写转换最简的公式?

A:153字符

=TEXT(B7,"yyyy[dbnum2]年")&IF(OR(MONTH(B7)<3,MONTH(B7)=10),"零",)&TEXT(B7,"m[dbnum2]月")&IF(OR(DAY(B7)<11,DAY(B7)=20,DAY(B7)=30),"零",)&TEXT(B7,"d日[dbnum2]")

131字符
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(B7,"yyyy年mm月dd日[dbnum2]"),"壹拾月","零壹拾月"),"壹拾日","零壹拾日"),"贰拾日","零贰拾日"),"叁拾日","零叁拾日")

119字符
  • =TEXT(B7,"yyyy年"&IF(OR(MONTH(B7)<3,MONTH(B7)=10),"零",)&"m月"&IF(OR(DAY(B7)<11,DAY(B7)=20,DAY(B7)=30),"零",)&"d日[dbnum2]")

116字符(方便套打)
  • =TEXT(B8,"e "&IF(OR(MONTH(B8)<3,MONTH(B8)=10),"零",)&"m "&IF(OR(DAY(B8)<11,DAY(B8)=20,DAY(B8)=30),"零",)&"d [dbnum2]")

106字符(方便套打)
  • =TEXT(B8,"e "&IF(OR(MONTH(B8)<3,MONTH(B8)=10),"零",)&"m "&IF(OR(DAY(B8)=20,DAY(B8)=30),"零",)&"dd [dbnum2]")

96字符,进一步简化,终于过100字符大关!
  • =TEXT(B8,"e年[dbnum2]"&IF(OR(MONTH(B8)<3,MONTH(B8)=10),"零",)&"m月"&IF(MOD(DAY(B8),10),,"零")&"dd日")

90字符,进一步简化,用数组又减去6个字符!
  • =TEXT(B8,"e年[dbnum2]"&IF(OR(MONTH(B8)={1,2,10}),"零",)&"m月"&IF(MOD(DAY(B8),10),,"零")&"dd日")