Number of years, months, days between two dates.

J

JE McGimpsey

This seems to be self-consistent.

However, it's strange to me that one can't get a result of of 0 1 0, 0 3
0, 0 5 0, 0 8 0, or 0 10 0 from the date 1/31/2005. How do you go from
2/28/2005 being less than a month to 3/1/2005 being 1 day more than a
month, when one never had a date that was a month later?
 
M

Mangesh

Hi Ron,

Thanks for pointing out the bug yet again. This should sort it out again.

=(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)>MONTH(B1),AND(MON
TH(A1)=MONTH(B1),DAY(A1)>DAY(B1)))),1,0) & " " &
(MONTH(B1)-MONTH(A1))-IF(AND(MONTH(A1)<MONTH(B1),DAY(A1)>DAY(B1)),1,0)+IF(AN
D(YEAR(A1)<YEAR(B1),MONTH(A1)>MONTH(B1),DAY(A1)<=DAY(B1)),12,0)+IF(AND(YEAR(
A1)<YEAR(B1),MONTH(A1)>=MONTH(B1),DAY(A1)>DAY(B1)),11,0)+IF(AND(MONTH(A1)<>M
ONTH(A1+1),MONTH(B1)<>MONTH(B1+1),DAY(A1)>DAY(B1)),1,0) & " " &
IF(DAY(B1)>=DAY(A1),DAY(B1)-DAY(A1),IF(DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY
(A1)<0,0,DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY(A1))+DAY(B1))-IF(AND(MONTH(A1
)<>MONTH(A1+1),MONTH(B1)<>MONTH(B1+1),DAY(A1)>DAY(B1)),DAY(B1),0)


A1 = start date
B1 = end date

I agree with the first one and it should have been 0 1 0 and not 0 0 28.
(Atleast thats my intention). As for the second one, I consider a month to
get over on the same date next month, so it is as intended.

Just saw the post by JE.

<QUOTE>
This seems to be self-consistent.

However, it's strange to me that one can't get a result of of 0 1 0, 0 3
0, 0 5 0, 0 8 0, or 0 10 0 from the date 1/31/2005. How do you go from
2/28/2005 being less than a month to 3/1/2005 being 1 day more than a
month, when one never had a date that was a month later?
</QUOTE>

There was a bug and this one should sort it out.

Regards

Mangesh
 
J

JE McGimpsey

Hmmm... no longer self-consistent:


A B C
1 1/28/2005 2/28/2005 0 1 0
2 1/29/2005 2/28/2005 0 0 30
3 1/30/2005 2/28/2005 0 0 29
4 1/31/2005 2/28/2005 0 1 0
5 2/01/2005 2/28/2005 0 0 27
 
R

Ron Rosenfeld

Hi Ron,

thanks for the bug. The following should sort it out.

=(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)>MONTH(B1),AND(MON
TH(A1)=MONTH(B1),DAY(A1)>DAY(B1)))),1,0) & " " &
(MONTH(B1)-MONTH(A1))-IF(AND(MONTH(A1)<MONTH(B1),DAY(A1)>DAY(B1)),1,0)+IF(AN
D(YEAR(A1)<YEAR(B1),MONTH(A1)>MONTH(B1),DAY(A1)<=DAY(B1)),12,0)+IF(AND(YEAR(
A1)<YEAR(B1),MONTH(A1)>=MONTH(B1),DAY(A1)>DAY(B1)),11,0) & " " &
IF(DAY(B1)>=DAY(A1),DAY(B1)-DAY(A1),IF(DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY
(A1)<0,0,DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY(A1))+DAY(B1))

A1 is start date, and B1 is end date.

Rules:
1. Exclude the start date, and include the end date; as excel does in its
own calculations. For e.g.
23/05/2005 - 22/05/2005 = 1 day

2. As for your other 2 queries:

Here, the year and month is completed on the same 'day' (28 of start month)
of the previous month (dec 2005) of the end day (jan 2006). So the new start
date for the calculation of the number of days only is 28 dec 2005. From
here on the remaining days are calculated. If this new start date falls on
say 30 feb (error intended after the above calculation), then it is rounded
down to a valid end date like 28 feb for non-leap years and 29 feb for leap.

Comments awaited....

I think I may have come across an "illogicality" in your formula.

StartDate: 12/31/2004
End Date: 4/30/2004

(or any similar sequence where the ending month ends prior to the beginning
bonth).

To my way of thinking, this should give a result of 4 months, exactly.

Your formula gives 3 months 30 days


By the way, for interest, here is a UDF I cobbled together to do it the way *I*
would want to see it. In other words, Calendar Months (full months) plus add
on the extra days at the beginning and end. This can leave one with a result
of

1/1/2005
4/29/2005

0 yrs 2 months 59 days

Since the only full months in this sequence are February and March.

As written, the UDF also requires a reference to the ATP, but the eomonth
function could certainly be rewritten in VBA.

===================================
Function CalendarMonthsAndDays(d1 As Date, d2 As Date) As String
Dim temp As Date
Dim i As Double
Dim yr As Long, mnth As Long, dy As Long

Do Until temp >= d2
i = i + 1
temp = eomonth(d1, i)
Loop

If temp <> d2 Then
i = i - 1
End If

yr = Int(i / 12)
mnth = i Mod 12
dy = d2 - eomonth(d1, i) + (eomonth(d1, 0) - d1)

CalendarMonthsAndDays = yr & " yrs " & mnth & " months " & dy & " days"
End Function
==================================
--ron
 
M

Mangesh

Hi JE, Ron,


Ok. another minor adjustment:

=(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)>MONTH(B1),AND(MON
TH(A1)=MONTH(B1),DAY(A1)>DAY(B1)))),1,0) & " " &
(MONTH(B1)-MONTH(A1))-IF(AND(MONTH(A1)<MONTH(B1),DAY(A1)>DAY(B1)),1,0)+IF(AN
D(YEAR(A1)<YEAR(B1),MONTH(A1)>MONTH(B1),DAY(A1)<=DAY(B1)),12,0)+IF(AND(YEAR(
A1)<YEAR(B1),MONTH(A1)>=MONTH(B1),DAY(A1)>DAY(B1)),11,0)+IF(AND(MONTH(B1)<>M
ONTH(B1+1),DAY(A1)>DAY(B1)),1,0) & " " &
IF(DAY(B1)>=DAY(A1),DAY(B1)-DAY(A1),IF(DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY
(A1)<0,0,DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY(A1))+DAY(B1))-IF(AND(MONTH(B1
)<>MONTH(B1+1),DAY(A1)>DAY(B1)),DAY(B1)+DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-
DAY(A1),0)


A1 = start date
B1 = end date

Ron, my whole idea at the moment is to avoid using any VBA and the analysis
toolpak. And thats why I am trying to work my way through simple if
conditions.


Mangesh
 
J

JE McGimpsey

Hate to be continually sniping, but

A B C
1 11/30/2005 02/28/2005 0 3 1
2 11/30/2005 03/01/2005 0 3 1
 
M

Mangesh

Hi JE,

no problem. But I forgot to mention that the start date has to be less than
the end date. So in essence, your query should be

28-Feb-05 30-Nov-05 0 9 2
1-Mar-05 30-Nov-05 0 8 29


And it does give me proper results. The other way round would give some
negative results in month.

Mangesh
 
J

JE McGimpsey

It was an error in my transcription. Try 11/30/2004. You get the same
result for both 2/28/2005 and 3/1/2005
 
R

Ron Rosenfeld

Ron, my whole idea at the moment is to avoid using any VBA and the analysis
toolpak. And thats why I am trying to work my way through simple if
conditions.

Sometimes, VBA is easier.

It is certainly simpler to debug <g>.

Avoiding the ATP is easy. I would just have to write an EOMONTH function in
VBA.

With your latest iteration, I think the following VBA routine mimics it, and
seems simpler:
==========================
Function DateIntvl(d1 As Date, d2 As Date) As String
Dim temp As Date
Dim i As Double
Dim yr As Long, mnth As Long, dy As Long

Do Until temp > d2
i = i + 1
temp = DateAdd("m", i, d1)
Loop

i = i - 1
temp = DateAdd("m", i, d1)

yr = Int(i / 12)
mnth = i Mod 12
dy = d2 - temp

DateIntvl = yr & " yrs " & mnth & " months " & dy & " days"

End Function
=======================

However, both it and your latest formula give the following results, which seem
less than useful:

1/28/2005 2/28/2005 0 1 0
1/29/2005 2/28/2005 0 1 0
1/30/2005 2/28/2005 0 1 0
1/31/2005 2/28/2005 0 1 0

---------------------------------

My Calendar Month routine, (rewritten below so as to avoid the ATP reference),
gives the following results for those same date intervals:

1/28/2005 2/28/2005 0 yrs 1 months 3 days
1/29/2005 2/28/2005 0 yrs 1 months 2 days
1/30/2005 2/28/2005 0 yrs 1 months 1 days
1/31/2005 2/28/2005 0 yrs 1 months 0 days

====================================
Function CalendarMonthsAndDays(d1 As Date, d2 As Date) As String
Dim temp As Date
Dim i As Double
Dim yr As Long, mnth As Long, dy As Long

Do Until temp >= d2
i = i + 1
temp = EOM(d1, i)
Loop

If temp <> d2 Then
i = i - 1
End If

yr = Int(i / 12)
mnth = i Mod 12
dy = d2 - EOM(d1, i) + (EOM(d1, 0) - d1)

CalendarMonthsAndDays = yr & " yrs " & mnth & " months " & dy & " days"
End Function
'---------------------
Function EOM(DT As Date, mnths As Double) As Date
Dim Day1ofDT As Date
Dim temp As Date

Day1ofDT = DT - Day(DT) + 1

'add requisite number of months
temp = DateAdd("m", mnths, Day1ofDT)

'go to end of month

EOM = temp + 32 - Day(temp + 32)

End Function
===============================


--ron
 
M

Mangesh

Hi Ron,

I agree with you that sometimes VBA is an easier option. It was just that
the query had come in the worksheet.functions group and I set myself upon
the task of writing the formula. But it seems to be requiring some effort.
If I get some time, will try to sort it out.

By the way, thanks for all those tests by you and JE.

Regards
Mangesh
 
M

Mangesh Yadav

Hi JE,

Thanks to you also for all the efforts. Will try to work on it once I get
some time.

Mangesh
 
J

Jesline

Hi

If let say I have start date: 15/12/04 and end date : 28/3/05 for a loan
rollover period from last year.

I need a formula to auto calculate the number of days for Jan 05, Feb 05 and
Mar 05 separately in different rows. how should I go about it?
 
R

Ron Rosenfeld

Hi

If let say I have start date: 15/12/04 and end date : 28/3/05 for a loan
rollover period from last year.

I need a formula to auto calculate the number of days for Jan 05, Feb 05 and
Mar 05 separately in different rows. how should I go about it?

A formula for days in a month, with any date in that month in A1, is:

=32-DAY(A1-DAY(A1)+32)


--ron
 
A

Arvi Laanemets

Hi

A1="StartDate"
A2="EndDate"
A3="Days1"
A4="Days2"
etc.

Into B1 and B2 enter start and end dates respectively.
B3=IF(DATE(YEAR($B$1),MONTH($B$1)+ROW()-3,1)<$B$2,(DAY(MIN($B$2,DATE(YEAR($B$1),MONTH($B$1)+ROW()-2,0)))-DAY(MAX($B$1,DATE(YEAR($B$1),MONTH($B$1)+ROW()-3,1)))+1),"")

Copy the formula in B3 down for as much rows you think you will need months
maximally. (Start and end dates are included into count of days, for
abundant months nothing - an empty string - is returned.)
 
G

Grizzly Bear

Hey all...

I thought I might tackle the year - month - day problem, but I have a
few questions first.

From the previous posts I concluded that a full month equals both of
the following:

An end date that is the same day of the month as the start date
ex. 15 Jan 2005 & 15 Feb 2005 should be 0-1-0

An end date that is the end of the month
ex. 28 Feb 2005 counts as a full month presuming that the start date is
prior to Feb.

This brings up a problematic question....

If the start month has more days than the end month AND the end date is
the last day of the month while the start date is not eom but still >=
the end date.....

Ex:
Start: 28 Jan 2005
END: 28 Feb 2005
Should the result be 0-1-0 or 0-1-3? ( the 28th to the 28th is 1 month
so what about the 3 days left in Jan?)

It get even weirder as you progress the dates:
28 Jan 2005 to 27 Feb 2005 = 0-0-30 (3 days in Jan + 27 in Feb)

28 Jan 2005 to 28 Feb 2005 = 0-1-0 ? or 0-1-3 (3 days left in Jan +
Feb, yet the 28th to the 28th should be a month) but how can you go
from 0-0-30 to 0-1-3 by changing just one day?

28 Jan 2005 to 01 Mar 2005 = 0-1-1 ? or 0-1-4 (3 days left in Jan + Feb
+ 1 day March yet 1-28 to 2-28=1 month +1 day for March))

29 Jan 2005 to 01 Mar 2005 = 0-1-3 (2 days left in Jan + Feb + 1 day
March)

30 Jan 2005 to 01 Mar 2005 = 0-1-2 (1 day left in Jan + Feb + 1 day
March)

Anyway, notwithstanding clarification on this problem, here is my
solution:

=IF(MONTH(B1)<MONTH(A1),YEAR(B1)-YEAR(A1)-1,YEAR(B1)-YEAR(A1))&" Years
"&
IF(MONTH(A1)>MONTH(B1),IF(DAY(B1)=DAY(EOMONTH(B1,0)),MONTH(B1)-MONTH(A1)+12,MONTH(B1)-MONTH(A1)+11),IF(DAY(B1)=DAY(EOMONTH(B1,0)),MONTH(B1)-MONTH(A1),IF(DAY(B1)>DAY(A1),MONTH(B1)-MONTH(A1),MONTH(B1)-MONTH(A1)-1)))&"
Months "&
IF(DAY(B1)=DAY(EOMONTH(B1,0)),DAY(EOMONTH(A1,0))-DAY(A1),IF(DAY(B1)<DAY(A1),DAY(EOMONTH(A1,0))-DAY(A1)+DAY(B1),DAY(B1)-DAY(A1)))&"
Days"

GrizzlyBear
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top