Prev: Count using IF / AND
Next: 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 <clarar...(a)hotmail.com> 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 <clararyan(a)hotmail.com> 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 / AND
Next: ranking