以A1单元格的身份证为例,处理各种小需求
身份证计算年龄(15位、18位)
=IFERROR(
IF(
LEN(A1)=15,
DATEDIF(DATE(MID(A1,7,2)+1900,MID(A1,9,2),MID(A1,11,2)),TODAY(),"y"),
IF(
(LEN(A1)=17)+(LEN(A1)=18)>0,
DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),TODAY(),"y"),
DAY(x)
)
)&"岁",
"身份证格式错误"
)
身份证计算性别(15位、18位)
=IFERROR(
CHOOSE(
MOD(
IF(
LEN(A1)=15,
MID(A1, 15, 1),
IF(
(LEN(A1)=17)+(LEN(A1)=18)>0,
MID(A1, 17, 1),
DAY(x)
)
),
2
)+1,
"女", "男"
),
"身份证格式错误"
)
计算身份证校验码
=IF(
(LEN(A1)=17)+(LEN(A1)=18)>0,
CHOOSE(
MOD(
7 * MID(A1, 1, 1) +
9 * MID(A1, 2, 1) +
10 * MID(A1, 3, 1) +
5 * MID(A1, 4, 1) +
8 * MID(A1, 5, 1) +
4 * MID(A1, 6, 1) +
2 * MID(A1, 7, 1) +
1 * MID(A1, 8, 1) +
6 * MID(A1, 9, 1) +
3 * MID(A1, 10, 1) +
7 * MID(A1, 11, 1) +
9 * MID(A1, 12, 1) +