Prev: Count using IF / ANDNext: ranking From: Newfie809 on 28 May 2010 15:17 June 30, 2010 A B C D Birth Date Date of Hire Appt. Int/Act. Appt. VP November 22, 1962 September 1, 1988 N/A September 1, 1998 Total Admin Service Total Bd Service E F 11.84 21.84 I am usint June 30 , 2010 as the end date for the formula. From Sept 1,1998 to June 30 I get 11.84 years. this should be 11 years 10 months and then From Sept 1, 1988 to June 30, 2010 should be 21 years 10 months. I tried to round up but it would not let me. Can anyone help me figure this out. -- Newfie From: Ziggy on 28 May 2010 16:58 On May 28, 1:17 pm, Newfie809 wrote:> June 30, 2010 > > A                                  B                              C                       >   D > Birth Date                             Date of Hire        Appt. Int/Act.       Appt. VP > > November 22, 1962          September 1, 1988           N/A                September 1, 1998 > > Total Admin Service     Total Bd Service >             E                             >          11.84        21.84 > > I am usint June 30 , 2010 as the end date for the formula.  From Sept 1,1998 > to June 30 I get 11.84 years. this should be 11 years 10 months and then From > Sept 1, 1988 to June 30, 2010 should be 21 years 10 months.  I tried to round > up but it would not let me.  Can anyone help me figure this out. > > -- > Newfie Using the YEARFRAC function I get the same number and decimal. You can ROUND up or down from there =ROUND(YEARFRAC(DATE1,DATE2,0),0) for a whole year solution. To give a number and text answer my solution is a bit ugle but it workds for me. ..~=ROUNDDOWN(YEARFRAC(W5408,W5409,0),0)&" Years "&ROUND((YEARFRAC(W5408,W5409,0)-ROUNDDOWN(YEARFRAC(W5408,W5409,0), 0))*12,0)&" Months" It rounds down the 11.84 to 11 (for the year value), and the subtracts 11 from the 11,84, muliplies by 12 for the number of months and rounds that number up. Not pretty but it works. Answer will be: 11 Years 10 Months Sig From: Gord Dibben on 28 May 2010 17:09 You have a typo or two in your description but try this. June 30, 2010 in A1 September 1, 1998 in A3 In a cell enter =DATEDIF(A3,A1,"y")&" years, "&DATEDIF(A3,A1,"ym")&" months, " & DATEDIF(A3,A1,"md") & " days" Returns 11 years, 9 months, 29 days Where you get the 21 years 10 months is a mystery to me. Gord Dibben MS Excel MVP On Fri, 28 May 2010 12:17:01 -0700, Newfie809 wrote: >June 30, 2010 > >A B C > D >Birth Date Date of Hire Appt. Int/Act. Appt. VP > >November 22, 1962 September 1, 1988 N/A September 1, 1998 > > >Total Admin Service Total Bd Service > E F > 11.84 21.84 > >I am usint June 30 , 2010 as the end date for the formula. From Sept 1,1998 >to June 30 I get 11.84 years. this should be 11 years 10 months and then From >Sept 1, 1988 to June 30, 2010 should be 21 years 10 months. I tried to round >up but it would not let me. Can anyone help me figure this out.  |  Pages: 1 Prev: Count using IF / ANDNext: ranking