If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date formula rounded up
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 |
#2
|
|||
|
|||
Date formula rounded up
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 |
#3
|
|||
|
|||
Date formula rounded up
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. |
Thread Tools | |
Display Modes | |
|
|