|
每年生日勇哥都会收到HR的生日祝福，非常很感动。有次问HR MM 是怎么做到的。MM告诉勇哥是每天打开一个个看的，真是辛苦HR MM了。本文将介绍如何使用条件格式完成员工生日自动标识。 定义如下图所示的员工信息表 有员工过阴历生日因此需要根据阴历生日转为成对应的阳历生日。根据员工选择的生日类型。将当年实际的生日放在"生日"列. 为了找出阳历对应的阴历，需要建立如下表所以的对应关系 第一步:根据阳历生日找出对应的农历生日 LOOKUP(G5,出生日期:农历月份)&INDEX(农历日期,G5-LOOKUP(G5,出生日期)+1) 根据lookup找出阳历生日对应的农历月份 使用lookup的模糊匹配功能找到阳历日期对应小于等于阳历日期的最大值结合使用index找出对应的农历日期 温馨提示:这里使用lookup的模糊匹配功能，找到小于等于查找值对应的最大值。 第二步:计算出农历月天数 根据当前的年份找出使用match找出年份的偏移 =MATCH(YEAR(TODAY()),Q:Q,0) aa 在找到对应的年份偏移后再在相应区域中找出对应的月份偏移 找出年份对应的偏移量MATCH(YEAR(TODAY(),Q:Q,0) 计做a =MATCH(LEFT(H3,2),INDIRECT("s"& a & ":s5000"),0) bb 使用Index找到阴历月份对应的天数 =INDEX(T:T,aa+bb-1) 第三步:因为农历生日有三十号，而当月农历无三十号时，按二十九号过生日。因此需要加上日期调整 =IF(AND(RIGHT(H3,2)="三十",I3=29),1,0) 第四步:找出农历生日对应本年的阳历生日 LOOKUP(LEFT(H3,2),OFFSET(INDIRECT("U" & a),0,0,12,1),OFFSET(INDIRECT("T" & a),0,0,12,1)) 找出相应阴历月份对应的第一天 计做b MATCH(RIGHT(H3,2),$W:$W,0)- 1 找出阴历日期对应的日期 计做 c 阳历生日 = b+c if(日期调整 = 1 ， b+c-1，b+c) 计做d 第五步:根据过阴历生日还是阳历是日，计算出本年哪天过生日 if(过阳历生日，DATE(YEAR(TODAY()),MONTH(G3),DAY(G3)),d) 上述五步计算出哪天过生日,使用条件格式。为不同日期设置不同的格式。直接使用按颜色筛选就可以知道 当天 明天 本周 本月 下月过生日的员工信息。 有了自动的颜色标识，再也不需要一个个盯着找 过生日的员工了。 |
|
|
