最终幻想15mod(套函数的公式)

表一：函数公式

1、身份证长度

=LEN(B2) =LENB(B2)

2、 性别

=IF(ISODD(MID(B2,17,1)),"男","女")

=IF(MOD(MID(B2,17,1),2),"男","女")

=IF(ISEVEN(MID(B2,17,1)),"女","男")

15位、18位同时判断性别

=IF(LEN(B4)=18,IF(MOD(MID(B4,17,1),2),"男","女"),IF(MOD(RIGHT(B4,1),2),"男","女"))

=IF(MOD(MID(B2,INT((LEN(B2)-1)/2)*2+1,1),2),"男","女")

=TEXT(MOD(0&MID(A5,15,3),2)-(A5=""),"男;;女")

=TEXT(ISODD(0&MID(A5,9,9))-(A5=""),"男;;女")

4、出生日期

=TEXT(MID(B2,7,8),"0-00-00")

=TEXT(MID(B2,7,8),"0年00月00日")

5、年龄

=DATEDIF(B8,TODAY(),"y")

=DAYS360(B8,TODAY(),12)/360

=YEARFRAC(B8,TODAY())

6、退休日期(I列)

=TEXT(EDATE(G3,12*(5*(F3="男")+55)),"yyyy/mm/dd aaaa")

7、籍贯(M列)

=VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,)

表二：函数公式

8、社会工龄(T列)

=DATEDIF(S3,NOW(),"y")

9、公司工龄(W列)

=DATEDIF(V3,NOW(),"y")&"年"&DATEDIF(V3,NOW(),"ym")&"月"&DATEDIF(V3,NOW(),"md")&"天"

10、合同续签日期(Y列)

=DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1

11、合同到期日期(Z列)

=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY(),"[

12、工龄工资(AA列)

=MIN(700,DATEDIF($V3,NOW(),"y")*50)

13、生肖(AB列)

=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",M