Number of Years, Months & Days from Start Date 1

[LMcleod]

Hi,

I need to get a function to produce years, months and days from a given Start Date up till Today.

Here is what I've come up with so far - the years and days arre working fine:

Years =YEAR(TODAY())-YEAR($A2)

Days =IF(DAY(TODAY())<DAY(A2),DAY(DATE(YEAR(A2),MONTH(A2)+1,DAY(1))-1)+DAY(TODAY())-DAY(A2),DAY(TODAY())-DAY(A2))

Months - this is the one that's wrong!! It returns FALSE if the Start Date's month is less than TODAY's month (month being Jan = 1, Feb = 2 etc. regardless of year)

=IF(MONTH(TODAY())<MONTH(A7), IF(DAY(TODAY())<DAY(A7),12-MONTH(A7)+MONTH(TODAY())-1,12-MONTH(A7)+MONTH(TODAY())),IF(MONTH(TODAY())>MONTH(A7),IF(DAY(TODAY())<DAY(A7),MONTH(TODAY())-MONTH(A7)-1),IF(DAY(TODAY())>DAY(A7),MONTH(TODAY())-MONTH(A7))))

Can anyone help me?

Also, if you know of a function I have not used that would be easier - please tell me!

Thanks

Louise

 

[KenWright]

How about:-

=DATEDIF(A1,A2,&quot;m&quot

with the earliest date in A1, latest in A2.

Can also use same for Years and days, though for days a simple =A2-A1 will give you what you need.

Regards
Ken.............

 

[LMcleod]

No, I have looked at this function, but that gives me the total difference in months. For example, if the start date is 14/04/02 and today is 15/04/02 (which it is!) I need the answer to be:
1 year ( this one I have worked out correctly)
0 months (this is the one I have trouble with) and
1 day (this is correct also)

It's just that one formula that I'm having trouble with, this one that calculates the months.

=IF(MONTH(TODAY())<MONTH(A8), IF(DAY(TODAY())<DAY(A8),12-MONTH(A8)+MONTH(TODAY())-1,12-MONTH(A8)+MONTH(TODAY())),IF(MONTH(TODAY())>MONTH(A8),IF(DAY(TODAY())<DAY(A8),MONTH(TODAY())-MONTH(A8)-1),IF(DAY(TODAY())>DAY(A8),MONTH(TODAY())-MONTH(A8))))

Thanks for you help anyway.

Louise

 

[uberskunk]

Hi,

It's not quite a solution but it should help some.

x = &quot;01/02/1999&quot;

y = DateDiff(&quot;yyyy&quot;, x, Now())
m = DateDiff(&quot;m&quot;, x, Now())
d = DateDiff(&quot;d&quot;, x, Now())

I realise the code isn't a cell function but I was hoping you could use it anyway.

I think that after y = .... you will have to add that value onto startdate to return the months.

And the same for the days.

y = DateDiff(&quot;yyyy&quot;, x, Now())
x = DateAdd(&quot;yyyy&quot;, y, x)
m = DateDiff(&quot;m&quot;, x, Now())
x = DateAdd(&quot;m&quot;, m, Now())
d = DateDiff(&quot;d&quot;, x, Now())

y = years
m = month
d = day

Hope this helps.
Mike

 

[Zathras]

1. What is the number of months/days between 5 January 2000 and 25 March 2000?

2. What is the number of months/days between 5 January 2002 and 25 March 2002?

3. What is the number of months/days between 5 June 2002 and 25 August 2002?

4. What is the number of months/days between 5 July 2002 and 25 September 2002?

 

[LMcleod]

Zathras,

It will be 2 months 20 days.

 

[LMcleod]

Uberskunk,

Sorry, I don't think I explained too well what I needed. I'm after the difference between 2 dates, but (see above) the difference between 5 July 2002 and 25 September 2002 will be &quot;2 months 20 days&quot;, NOT &quot;2 months&quot; and NOT &quot;81 days&quot;

I hope this makes it clearer.

Louise

 

[xlbo]

I think what Zathras is getting at is that a month is an arbitrary amount of time - anything from 28 to 31 days, therefore to show something in Years, months and days is really not telling you anything cos it could be any of several numbers of days. If you want to show how long something has been going on, you should show it in an absolute time measurement like days or weeks - 1 year and 2 months from x to y could conceivably be several days shorter or longer than 1 year and 2 months from a to b...

Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[Zathras]

OK. One more set of questions and I think we will understand the algorithm. Only after we know the algorithm you want, can it be expressed in code. (Spot on, Geoff!)

1. What is the number of months/days between 25 February 2000 and 5 March 2000?

2. What is the number of months/days between 25 February 2002 and 5 March 2002?

3. What is the number of months/days between 25 June 2002 and 5 July 2002?

4. What is the number of months/days between 25 July 2002 and 5 August 2002?

 

[LMcleod]

xlbo,

I see what you're saying - I'd like to assume that it is 1 month between any 1 date in one month and the same date in the following month, regardless of the month being 28, 29, 30 or 31 days.

Mmmmm...I can see this becoming tricky.

You would think there would be a function that would do this in 1 formula. It must be something alot of people would find useful.

 

[LMcleod]

Zathras,

1) 9 days

2) 8 days

3) 10 days

4) 11 days

 

[LMcleod]

I've got a spreadsheet that I'm using to test this.

If anyone is interested, then send me your email.

I'm off home now, but I'll be checking the forum tomorrow morning.

 

[Zathras]

Plug this into a new spreadsheet:

A1: 'Date1
B1: 'Date2
C1: 'Elapsed
A2: 2/25/2000

 'or 25/2/2000 as appropriate

B2: 3/5/2000

 'or 5/3/2000 as appropriate

C2: =INT(($B2-$A2)/365)&" years "&MOD(MONTH(B2)-MONTH(A2)-IF(DAY(B2)>=DAY(A2),0,1),12)&" months "&IF(DAY(B2)>=DAY(A2),DAY(B2)-DAY(A2),DAY(DATE(YEAR(B2),MONTH(B2),1)-1)-(DAY(A2)-DAY(B2)))&" days"

Try various values for Date1 and Date2 to see the results.

 

[KenWright]

Depending on how how exact this all needs to end up with in terms of days (as per previous posts), then you can still use the Datedif function to get your months:-

=DATEDIF(A2,A1,&quot;m&quot-FLOOR(DATEDIF(A2,A1,&quot;m&quot,12)

will take out all the years and should leave you with the whole months.

The first part of the formula might return say 65 months.
The second part with the FLOOR function simply rounds down to the nearest mulitple of your chosen significance (in this case 12, for 12 months) - That will determine how many years are in the 65 months (60 [5 yrs * 12mths] is the rounded down value. One from the other is the balance of months after years removed

Regards
Ken..............

 

[LMcleod]

I will try these both this morning, and get back to you.

Thanks for your help.

 

[Zathras]

LMcleod, see thread68-628207 for a more correct solution to the question. My formula fails miserably for some combinations of dates around leap years when the difference is one day short of a full number of years.

This formula from Bowers74 seems to produce the correct result in all cases:

=DATEDIF(A1,TODAY(),"y")&" Y "&DATEDIF(A1,TODAY(),"ym")&" M "&DATEDIF(A1,TODAY(),"md")&" D"

My apologies if you used my formula and are finding incorrect results.

 

[Bowers74]

Thnks for the kudos Zathras.

LMcleod:

I posted two different formulas in the thread. The first one rounds a little funny, but the second one is the one that Zathras meant.




Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

I just posted the following thread that contains some basics on Excel's date functions. It is aimed more at beginners, but might still be worth looking at:

faq68-4037



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!