Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add rounded numbers using the rounded value | Excel Discussion (Misc queries) | |||
why is answer rounded up? | Excel Discussion (Misc queries) | |||
how to display rounded number in a cell w/formula | Excel Discussion (Misc queries) | |||
how do i add rounded numbers? | Excel Discussion (Misc queries) | |||
Need formula to have date in cell B2 rounded to first of next mont | Excel Worksheet Functions |