A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Date formula rounded up



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 08:17 PM posted to microsoft.public.excel.worksheet.functions
Newfie809
external usenet poster
 
Posts: 56
Default 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  
Old May 28th, 2010, 09:58 PM posted to microsoft.public.excel.worksheet.functions
Ziggy
external usenet poster
 
Posts: 47
Default 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  
Old May 28th, 2010, 10:09 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:35 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.