Date formula rounded up

[Newfie809]

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.

 

[Ziggy]

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 toround
up but it would not let me.  Can anyone help me figure this out.

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

 

[Gord Dibben]

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