本帖最后由 lisachen 于 2011-3-28 12:51 编辑 / Y, L1 Z! P2 O' X+ p
0 z9 _- l4 a+ z& O- w* a2 X第一题
$ w6 r7 _, b7 s! z( _: t=DATE((MID(C1,7,4)),MID(C1,11,2),MID(C1,13,2))
! N( v3 E; F( [2 h# D=DATEVALUE(MID(C1,7,4)&"-"&MID(C1,11,2)&"-"&MID(C1,13,2))
' K8 ?- m, W8 L) w0 A2 g7 W(以上没考虑15位老身份证)- z/ _$ R; u8 ^( Q3 z: E+ a4 o
=TEXT(19&MID(C1,7+(LEN(C1)=18)*2,6),"0-00-00")
9 a0 b) P7 D$ J% f# x0 x- n=TEXT(LEFT(19&RIGHT(C1,9+(LEN(C1)=18)),8),"0-00-00")0 ?, w2 |" j$ e8 I" z3 X0 u
=TEXT(RIGHT(19&LEFT(C1,13+(LEN(C1)=18)),8),"0-00-00")(跟上答案差不多)
1 F, i# e5 i; f) c: H( [9 h4 h5 `( C& D8 h( X
第二题3 `/ L8 s5 `7 G5 _" D
=IF(LOOKUP(HOUR(B2)*60+MINUTE(B2),{0,511,720},{0,1,0})=1,"迟到",LOOKUP(HOUR(B12)*60+MINUTE(B12),{0,720,1050},{0,"早退","出勤"}))
, L D( \' I- l2 k觉得做的不好,但一时想不起更好的答案
3 I- f! X& D2 o
. \) |; k1 D9 t: q$ N2 D附加题
. e D5 B4 Q% _# h$ R$ x% o2 c% ~=LOOKUP(DATEDIF(A38,TODAY(),"y"),{0,5,10,15,20,25},{0,10,20,30,40,50})+ V( S7 Z. a3 f
& n% a5 p ]1 w0 `& G* e, D/ V, r. z$ S& n0 f5 `8 {8 u! P
附加题$ {% |. G0 q8 O
% J+ J# r. E% T; i( Y0 d; l
=SUM(LOOKUP(DATEDIF(A38:A56,TODAY(),"y"),{0,5,10,15,20,25},{0,10,20,30,40,50}))/ O# M4 j( n8 r5 [0 g
|