ࡱ> [ bjbj 4ΐΐ 68)4] o)ii"fff((((((($+-n)fffff)))f|(f(&h&(#8N,R'(?)0o)b'+.v+. &(&(8+.^(|fffffff))fffo)ffff+.fffffffff : MS Excel - Date Functions Calculate Qualifying Service Create the following worksheet Select Column B Select Format > Cells>Date Select the long date format and click OK ABCDE1EmpNameDate of JoiningQS-YearsQS-MonthsQS-Days2A01 January 19803B01 February 19754C24 September 19805In Cell c2 enter =DATEDIF(B2,TODAY(),"Y") In Cell d2 enter =DATEDIF(B2,TODAY(),"YM") In Cell e2 enter =DATEDIF(B2,TODAY(),"MD") Copy this formula to the cells below. Add more data and check Notes: Year function takes the year of the specific date; month takes the month part of the date; and day takes the date part of the specific date. Calculate Retirement Date Create the following worksheet ABC1Emp NameDOBRetirement Date2A01 December 19483B01 March 19605C01 February 19616D02 February 19607E03 February 1961 Enter the following formula as it is in cell C2 =EOMONTH(DAY(B2)&"/"&MONTH(B2)&"/"&(YEAR(B2)+60),0) This will give you the last working day of the month after adding sixty years to the year of birth. In case the cell is indicating numbers instead of date, please change the format of the cell by selecting Format > Cells>Date and select long date. All those who are born on 1st of the month will have to retire the previous month. Please enter the following formula for that. Please do not leave any space in between. This has to be written continuously in the formula bar. If the formula is not working check for errors in typing. =IF(DAY(B2)=1,EOMONTH(DAY(B2)&"/"&MONTH((B2)-1)&"/"&(YEAR(B2)+60),0),EOMONTH(DAY(B2)&"/"&MONTH(B2)&"/"&(YEAR(B2)+60),0)) All those who are born on 1st of January will retire on 31st December of the previous year after adding 60 years to the year of birth. Writing a long formula in one cell creates lot of problems. If there is any mistake the formula may not work properly. Sincere there are enough columns available, we can split the formula. Please do the following for this. ABCDE1Original Date of BirthNew MonthNew YearFinal Date of RetirementLong Month201/01/1960302/02/1960502/02/1961601/03/1960701/03/1961802/03/1960901/01/19611031/12/19601131/01/1960 Enter the following formula the respective cells In Cell B2 =IF(DAY(A2)=1,MONTH(A2-1),MONTH(A2)) In Cell C2 =IF(AND(DAY(A2)=1,MONTH(A2)=1),YEAR(A2-1),YEAR(A2))+60 In Cell D2 =1&"/"&B2&"/"&C2 In Cell E2 =EOMONTH((D2),0) You will get the following results ABCDE1Original Date of BirthNew MonthNew YearFinal Date of RetirementLong Month201/01/19601220191/12/201931 December 2019302/02/1960220201/2/202029 February 2020502/02/1961220211/2/202128 February 2021601/03/1960220201/2/202029 February 2020701/03/1961220211/2/202128 February 2021802/03/1960320201/3/202031 March 2020901/01/19611220201/12/202031 December 20201031/12/19601220201/12/202031 December 20201131/01/1960120201/1/202031 January 2020 Note: In Cell B2 we arrived at the month of retirement by checking the date of birth whether that is 1st or otherwise. In Cell C2 we arrived at the year of retirement by checking whether the date of birth is 1st of January or otherwise. We also added sixty years to the year of birth. In Cell C2 we converted the result to date format. In Cell D2 we found out the final date of retirement.     PAGE   PAGE \* MERGEFORMAT 1  67    8 9 @ A [ \ ֶym\ h/hCJOJQJ^JaJhoCJOJQJaJh/hoCJOJQJaJh/ho>*CJOJQJaJh/hT7CJOJQJaJh/h%9CJOJQJaJh/hw qCJOJQJaJ h/h?CJOJQJ^JaJh/h?CJOJQJaJh/CJOJQJaJh/h CJOJQJaJ7Wg$IfFfr $$Ifa$gd?gd? & Fgd?gdG5$a$gd kd$$Iflֈ A 9`  0 644 la]p<    $$Ifa$$If $$Ifa$gd?  kd$$Iflֈ A ''9'`''  0 644 la]p<      $$Ifa$$If $$Ifa$gd?  kd$$Iflֈ A ''9'`''  0 644 la]p< ! # 5 6 7 8 $$Ifa$$If $$Ifa$gd?8 9 kdI$$Iflֈ A ''9'`''  0 644 la]p<9 ; < = > ? @ $If $$Ifa$gd?@ A kd$$Iflֈ A ''9'`''  0 644 la]p<A k $$ P&#$/Ifa$gd$ P&#$/Ifgd & Fgd gd gdG5h^hgd? & Fgdw q & Fgdw q "kd $$Ifl\~&& &  6 P(0644 lap(yt ! 7 8 N O e g i k n o 9 ghѶѤѕхsdUh 5CJOJQJ\aJh]5CJOJQJ\aJ"h/hf)5CJOJQJ\aJh/hoCJH*OJQJaJh/hT7CJOJQJaJ"h/ho5CJOJQJ\aJhCJOJQJaJh/h CJOJQJaJh/hoCJOJQJaJ h/hCJOJQJ^JaJh/hCJOJQJaJ $$ P&#$/Ifa$gd$ P&#$/Ifgd 4""$ P&#$/Ifgdkd $$Ifl\~'& '  6 P 0644 lap(yt kd $$Ifl\~'' '  6 P 0644 lap(yt$$ P&#$/Ifa$gd    $$ P&#$/Ifa$gd$ P&#$/Ifgd ! # % 4""$ P&#$/Ifgdkd- $$Ifl\~'' '  6 P 0644 lap(yt% 6 7 8 kd=$$Ifl\~'' '  6 P 0644 lap(yt$$ P&#$/Ifa$gd8 : < M N $$ P&#$/Ifa$gd$ P&#$/IfgdN O Q S 4""$ P&#$/IfgdkdM$$Ifl\~'' '  6 P 0644 lap(ytS d e f kd]$$Ifl\~'' '  6 P 0644 lap(yt$$ P&#$/Ifa$gdf g h i j k l m n o 9 hi^gduI & Fgd gd/ & Fgdo & Fgd h^hgdogdo^gd*+,  013>BCEPTUWbfgitxy{νh CJOJQJ^JaJh/h CJOJQJaJh CJOJQJ^JaJ h/h CJOJQJ^JaJhuI5CJOJQJ\aJh 5CJOJQJ\aJ%h h 5CJH*OJQJ\aJ9 %0Ff$$ &#$/Ifa$gd $ &#$/Ifgd  01kd)$$Iflֈ~? ,'&q&xx  6  0644 lap(yt 13>?@AB$$ &#$/Ifa$gd $ &#$/Ifgd BCkdk$$Iflֈ~? ,''q'xx  6  0644 lap(yt CEPQRST$$ &#$/Ifa$gd $ &#$/Ifgd TUkd$$Iflֈ~? ,''q'xx  6  0644 lap(yt UWbcdef$$ &#$/Ifa$gd $ &#$/Ifgd fgkd$$Iflֈ~? ,''q'xx  6  0644 lap(yt gituvwx$$ &#$/Ifa$gd $ &#$/Ifgd xykd$$Iflֈ~? ,''q'xx  6  0644 lap(yt y{$$ &#$/Ifa$gd $ &#$/Ifgd kd[$$Iflֈ~? ,''q'xx  6  0644 lap(yt $$ &#$/Ifa$gd $ &#$/Ifgd kd$$Iflֈ~? ,''q'xx  6  0644 lap(yt $$ &#$/Ifa$gd $ &#$/Ifgd kd$$Iflֈ~? ,''q'xx  6  0644 lap(yt $$ &#$/Ifa$gd $ &#$/Ifgd #GS   DEGൣuufuuuufh/hqCJOJQJaJhqCJOJQJ^JaJ h/hqCJOJQJ^JaJhq5CJOJQJ\aJ"h h 5CJOJQJ\aJh 5CJOJQJ\aJh CJOJQJ^JaJh CJOJQJ^JaJh/h CJOJQJaJ h/h CJOJQJ^JaJ#kd$$Iflֈ~? ,''q'xx  6  0644 lap(yt $$ &#$/Ifa$gd $ &#$/Ifgd kdK $$Iflֈ~? ,''q'xx  6  0644 lap(yt Hgdq & Fgd ^gd   9DFf!#$$ &#$/Ifa$gd?Z$ &#$/Ifgd?ZDEkdQ%$$Iflֈ~ x '&&&  6  02644 lap(ytqEGRUZdu$If $$Ifa$$$ &#$/Ifa$gd?Z$ &#$/Ifgd?ZGuvx1234]^_`fhR㵢yuyuyuyuh?Zjh?ZU"h/huI5CJOJQJ\aJhuI5CJOJQJ\aJ%hqhq5CJH*OJQJ\aJhq5CJOJQJ\aJhqCJOJQJ^JaJ h/hqCJOJQJ^JaJh/hqCJOJQJaJhqCJOJQJ^JaJ-uvkd&$$Iflֈ~ x '&''  6  02644 lap(ytqvx$If $$Ifa$$$ &#$/Ifa$gd?Z$ &#$/Ifgd?Zkd'$$Iflֈ~ x '&''  6  02644 lap(ytq$If $$Ifa$$$ &#$/Ifa$gd?Z$ &#$/Ifgd?Zkd5)$$Iflֈ~ x '&''  6  02644 lap(ytq$If $$Ifa$$$ &#$/Ifa$gd?Z$ &#$/Ifgd?Zkd*$$Iflֈ~ x '&''  6  02644 lap(ytq 1$If $$Ifa$$$ &#$/Ifa$gd?Z$ &#$/Ifgd?Z12kd+$$Iflֈ~ x '&''  6  02644 lap(ytq24?AFO]$If $$Ifa$$$ &#$/Ifa$gd?Z$ &#$/Ifgd?Z]^kd-$$Iflֈ~ x '&''  6  02644 lap(ytq^`kns}$If $$Ifa$$$ &#$/Ifa$gd?Z$ &#$/Ifgd?Zkd].$$Iflֈ~ x '&''  6  02644 lap(ytq$If $$Ifa$$$ &#$/Ifa$gd?Z$ &#$/Ifgd?Zkd/$$Iflֈ~ x '&''  6  02644 lap(ytq$If $$Ifa$$$ &#$/Ifa$gd?Z$ &#$/Ifgd?Zkd0$$Iflֈ~ x '&''  6  02644 lap(ytqwR & Fgdqgdq & Fgdq$a$h]hgd] &`#$gd] "h/huI5CJOJQJ\aJh?ZhyCvmHnHuhuIjhuIUh' h' 0Jjh' 0JU21h:po/ =!"#$% p$$If]!vh55595`55#v#v#v9#v`#v:V l <0 6,55595`5a]p<kd$$Iflֈ A 9` <0 644 la]p<:$$If]!vh55595`55#v#v#v9#v`#v:V l  0 6,,55595`5a]p<@$$If]!vh55595`55#v#v#v9#v`#v:V l  0 6,,55595`59a]p<@$$If]!vh55595`55#v#v#v9#v`#v:V l  0 6,,55595`59a]p<@$$If]!vh55595`55#v#v#v9#v`#v:V l  0 6,,55595`59a]p<@$$If]!vh55595`55#v#v#v9#v`#v:V l  0 6,,55595`59a]p<2$$If!vh555 5#v#v #v:V l  6 P(06,55 59p(yt$$If!vh555 5#v#v #v:V l  6 P 06,,,55 59p(yt$$If!vh555 5#v#v #v:V l  6 P 06,55 59p(yt$$If!vh555 5#v#v #v:V l  6 P 06,55 59p(yt$$If!vh555 5#v#v #v:V l  6 P 06,55 59p(yt$$If!vh555 5#v#v #v:V l  6 P 06,55 59p(yt$$If!vh555 5#v#v #v:V l  6 P 06,55 59p(yt$$If!vh555q55x5x#v#v#vq#v#vx:V l  6 <06,555q55x9p<yt .kdm$$Iflֈ~? ,&&q&xx  6 <0644 lap<yt @$$If!vh555q55x5x#v#v#vq#v#vx:V l  6  06,,555q55x9p(yt :$$If!vh555q55x5x#v#v#vq#v#vx:V l  6  06,555q55x9p(yt :$$If!vh555q55x5x#v#v#vq#v#vx:V l  6  06,555q55x9p(yt :$$If!vh555q55x5x#v#v#vq#v#vx:V l  6  06,555q55x9p(yt :$$If!vh555q55x5x#v#v#vq#v#vx:V l  6  06,555q55x9p(yt :$$If!vh555q55x5x#v#v#vq#v#vx:V l  6  06,555q55x9p(yt :$$If!vh555q55x5x#v#v#vq#v#vx:V l  6  06,555q55x9p(yt :$$If!vh555q55x5x#v#v#vq#v#vx:V l  6  06,555q55x9p(yt :$$If!vh555q55x5x#v#v#vq#v#vx:V l  6  06,555q55x9p(yt :$$If!vh555q55x5x#v#v#vq#v#vx:V l  6  06,555q55x9p(yt $$If!vh55&5555#v#v&#v#v#v#v:V l  6 <026,55&55559p<ytq.kd!$$Iflֈ~ x &&&&  6 <02644 lap<ytqN$$If!vh55&5555#v#v&#v#v#v#v:V l  6  026,,55&55559p(ytqH$$If!vh55&5555#v#v&#v#v#v#v:V l  6  026,55&55559p(ytqH$$If!vh55&5555#v#v&#v#v#v#v:V l  6  026,55&55559p(ytqH$$If!vh55&5555#v#v&#v#v#v#v:V l  6  026,55&55559p(ytqH$$If!vh55&5555#v#v&#v#v#v#v:V l  6  026,55&55559p(ytqH$$If!vh55&5555#v#v&#v#v#v#v:V l  6  026,55&55559p(ytqH$$If!vh55&5555#v#v&#v#v#v#v:V l  6  026,55&55559p(ytqH$$If!vh55&5555#v#v&#v#v#v#v:V l  6  026,55&55559p(ytqH$$If!vh55&5555#v#v&#v#v#v#v:V l  6  026,55&55559p(ytqH$$If!vh55&5555#v#v&#v#v#v#v:V l  6  026,55&55559p(ytq^ 02 0@P`p2( 0@P`p 0@P`p 0@P`p 0@P`p 0@P`p 0@P`p8XV~_HmH nH sH tH @`@ NormalCJ_HaJmH sH tH V@V G5 Heading 3$<@&5CJOJQJ\^JaJDA`D Default Paragraph FontRi@R  Table Normal4 l4a (k (No List 4 @4 ]0Footer  !.)@. ] Page NumberHH  Balloon TextCJOJQJ^JaJp#p w q Table Grid7:V0$a$424 uIHeader  H$6/A6 uI Header CharCJaJ6/Q6 uI0 Footer CharCJaJPK![Content_Types].xmlj0Eжr(΢Iw},-j4 wP-t#bΙ{UTU^hd}㨫)*1P' ^W0)T9<l#$yi};~@(Hu* Dנz/0ǰ $ X3aZ,D0j~3߶b~i>3\`?/[G\!-Rk.sԻ..a濭?PK!֧6 _rels/.relsj0 }Q%v/C/}(h"O = C?hv=Ʌ%[xp{۵_Pѣ<1H0ORBdJE4b$q_6LR7`0̞O,En7Lib/SeеPK!kytheme/theme/themeManager.xml M @}w7c(EbˮCAǠҟ7՛K Y, e.|,H,lxɴIsQ}#Ր ֵ+!,^$j=GW)E+& 8PK!Ptheme/theme/theme1.xmlYOo6w toc'vuر-MniP@I}úama[إ4:lЯGRX^6؊>$ !)O^rC$y@/yH*񄴽)޵߻UDb`}"qۋJחX^)I`nEp)liV[]1M<OP6r=zgbIguSebORD۫qu gZo~ٺlAplxpT0+[}`jzAV2Fi@qv֬5\|ʜ̭NleXdsjcs7f W+Ն7`g ȘJj|h(KD- dXiJ؇(x$( :;˹! I_TS 1?E??ZBΪmU/?~xY'y5g&΋/ɋ>GMGeD3Vq%'#q$8K)fw9:ĵ x}rxwr:\TZaG*y8IjbRc|XŻǿI u3KGnD1NIBs RuK>V.EL+M2#'fi ~V vl{u8zH *:(W☕ ~JTe\O*tHGHY}KNP*ݾ˦TѼ9/#A7qZ$*c?qUnwN%Oi4 =3ڗP 1Pm \\9Mؓ2aD];Yt\[x]}Wr|]g- eW )6-rCSj id DЇAΜIqbJ#x꺃 6k#ASh&ʌt(Q%p%m&]caSl=X\P1Mh9MVdDAaVB[݈fJíP|8 քAV^f Hn- "d>znNJ ة>b&2vKyϼD:,AGm\nziÙ.uχYC6OMf3or$5NHT[XF64T,ќM0E)`#5XY`פ;%1U٥m;R>QD DcpU'&LE/pm%]8firS4d 7y\`JnίI R3U~7+׸#m qBiDi*L69mY&iHE=(K&N!V.KeLDĕ{D vEꦚdeNƟe(MN9ߜR6&3(a/DUz<{ˊYȳV)9Z[4^n5!J?Q3eBoCM m<.vpIYfZY_p[=al-Y}Nc͙ŋ4vfavl'SA8|*u{-ߟ0%M07%<ҍPK! ѐ'theme/theme/_rels/themeManager.xml.relsM 0wooӺ&݈Э5 6?$Q ,.aic21h:qm@RN;d`o7gK(M&$R(.1r'JЊT8V"AȻHu}|$b{P8g/]QAsم(#L[PK-![Content_Types].xmlPK-!֧6 +_rels/.relsPK-!kytheme/theme/themeManager.xmlPK-!Ptheme/theme/theme1.xmlPK-! ѐ' theme/theme/_rels/themeManager.xml.relsPK]  5558 G #5=Q    8 9 @ A % 8 N S f 01BCTUfgxyDEuv12]^  !"$%&'()*+,-./012346789:;<>?@ABCDEFGHIJKLMNOP /18!!8@0(  B S  ?  $ '  3333*+, - *+, - P$i,8a)ƚ0%x6ie^`o(. ^`hH. pL^p`LhH. @ ^@ `hH. ^`hH. L^`LhH. ^`hH. ^`hH. PL^P`LhH.h^`OJQJo(hH ^`hH. pLp^p`LhH. @ @ ^@ `hH. ^`hH. L^`LhH. ^`hH. ^`hH. PLP^P`LhH.^`o(. ^`hH. pLp^p`LhH. @ @ ^@ `hH. ^`hH. L^`LhH. ^`hH. ^`hH. PLP^P`LhH.h ^`hH.h ^`hH.h pLp^p`LhH.h @ @ ^@ `hH.h ^`hH.h L^`LhH.h ^`hH.h ^`hH.h PLP^P`LhH.^`o(. ^`hH. pLp^p`LhH. @ @ ^@ `hH. ^`hH. L^`LhH. ^`hH. ^`hH. PLP^P`LhH.i08a$i                                             VT^Gv ;z   /T7uIbLJMAN?ZBkqw qyCv' 2o?]%9G5f)>C @ 8 { x@UnknownG* Times New Roman5Symbol3. * ArialA. Arial Narrow5. *aTahoma;WingdingsA BCambria Math"1hftن]ئ_  !4  2Q HX ?22!xxDepartment of Atomic EnergyUserAbc      Oh+'0  $ D P \ ht|Department of Atomic EnergyUser Normal.dotmAbc19Microsoft Office Word@vE @گߓ'@r0@N, ՜.+,0 hp|    Department of Atomic Energy Title  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRTUVWXYZ[\]^_`abcdefghijklnopqrstuvwxyz{|}~Root Entry F,8N,Data S;21TablemK.WordDocument4SummaryInformation(DocumentSummaryInformation8CompObjy  F'Microsoft Office Word 97-2003 Document MSWordDocWord.Document.89q