員工關懷更Esay

每年生日勇哥都會收到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)

上述五步計算出哪天過生日,使用條件格式。為不同日期設置不同的格式。直接使用按顏色篩選就可以知道 當天 明天 本周 本月 下月過生日的員工信息。

有了自動的顏色標識，再也不需要一個個盯著找 過生日的員工了。