Number of years, months, days between two dates.

G

Guest

Hello.
I have a function that allows me to calculate the length of time between two
dates.

=YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1),
DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1)
<=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<MONTH(A1),DAY(B1)
=DAY(A1)),12,IF(AND(MONTH(B1)>MONTH(A1),DAY(B1)<DAY(A1)),-1)))&" months, "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days"
Click to expand...

I have a list of dates in adjacent columns. I want to be able to display the
result of this function in a third column, for the two dates in each row.
Can anyone tell me whether or not it is possible to somehow do this more
easily than pasting the function into each cell of the 3rd column and
changing the cell references in the funtion manually?
This is not practical as the columns are too large!
I only have a limited knowledge of Excel so I apologise if this is a simple
query.
Hope you clever people can help!
Many thanks
 
P

PC

Possibly this will work

=DATEDIF(A1,B1,"Y")&" Years "&DATEDIF(A1,B1,"YM")&" Months
"&DATEDIF(A1,B1,"Md")&" Days"

Startdate is in A1, Enddate is in B1

HTH

PC


Bluenose said:
Hello.
I have a function that allows me to calculate the length of time between two
dates.

=YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1),
DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1)
<=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<MONTH(A1),DAY(B1)
Click to expand...
"&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days"
 
J

JE McGimpsey

That will normally work, but note that DATEDIF assumes a month is as
long as the starting month (first argument), so if

A1: 31 January 2005
A2: 1 March 2005
A3: =DATEDIF(A1,B1,"Y")&" Years "&DATEDIF(A1,B1,"YM")&" Months " &
DATEDIF(A1,B1,"Md")&" Days"

will return

0 Years 1 Months -2 Days

There really isn't any consistent workaround, since "month" is not an
exact unit.
 
P

PC

Thanks JE

Didn't realize that DATEDIF would "error" that easily. (working with dates
is a huge pain)

PC
 
R

Ron Rosenfeld

Hello.
I have a function that allows me to calculate the length of time between two
dates.

=YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1),
DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1)
<=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<MONTH(A1),DAY(B1)

I have a list of dates in adjacent columns. I want to be able to display the
result of this function in a third column, for the two dates in each row.
Can anyone tell me whether or not it is possible to somehow do this more
easily than pasting the function into each cell of the 3rd column and
changing the cell references in the funtion manually?
This is not practical as the columns are too large!
I only have a limited knowledge of Excel so I apologise if this is a simple
query.
Hope you clever people can help!
Many thanks
Click to expand...

Any result expressed in years, months and days will be inexact since a "month"
can be anywhere from 28-31 days.

So is some rough approximation OK, or do you want to set up rules for what to
do if, for example, your dates are 31 Jan 2005; 1 Mar 2005.

Both your formula and the DATEDIF() formula posted by PC give a result of

0 years, 1 months, -2 days



--ron
 
A

Arvi Laanemets

Hi


JE McGimpsey said:
That will normally work, but note that DATEDIF assumes a month is as
long as the starting month (first argument), so if

A1: 31 January 2005
A2: 1 March 2005
A3: =DATEDIF(A1,B1,"Y")&" Years "&DATEDIF(A1,B1,"YM")&" Months " &
DATEDIF(A1,B1,"Md")&" Days"

will return

0 Years 1 Months -2 Days

There really isn't any consistent workaround, since "month" is not an
exact unit.
Click to expand...


This was a bad surprise for me - I have used DATEDIF quite often, and as I
now see, without checking it tgroughly before! How about this workaround
(days part only):

=DATEDIF(A1,B1,"MD")+(DAY(A1)>DAY(B1))*MAX(0,DAY(EOMONTH(A1,0))-DAY(EOMONTH(B1,-1)))
 
M

Mangesh

=YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1) & " " &
(MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12,0)
& " " &
IF(DAY(A2)-DAY(A1)<0,DAY(DATE(YEAR(A2),MONTH(A2),1)-1)+(DAY(A2)-DAY(A1)),DAY
(A2)-DAY(A1))

A1 = start date
A2 = End date

This should work fine enough. Tested 4 cases with it:
http://excelforum.com/showthread.php?t=371874


- Mangesh
 
M

Mangesh

You simply need to drag down (copy) your result in subsequent rows below. No
need to edit each time.

- Mangesh



Bluenose said:
Hello.
I have a function that allows me to calculate the length of time between two
dates.

=YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1),
DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1)
<=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<MONTH(A1),DAY(B1)
Click to expand...
"&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days"
 
R

Ron Rosenfeld

=YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1) & " " &
(MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12,0)
& " " &
IF(DAY(A2)-DAY(A1)<0,DAY(DATE(YEAR(A2),MONTH(A2),1)-1)+(DAY(A2)-DAY(A1)),DAY
(A2)-DAY(A1))

A1 = start date
A2 = End date
Click to expand...

It gives silly answer with

A1: 31 Jan 2005
A2: 1 Mar 2005
--ron
 
J

JE McGimpsey

Perhaps, but it gives some inconsistent answers...

A1: 12/30/2004
B1: 4/30/2004 ===> 0

Add 1 day:

B1: 5/1/2004 ===> 2

What happened to 1?

Another:

A1: 12/30/2004
A2: 2/28/2005 ===> 29

but

A2: 3/1/2005 ===> 2

Now we skip 0 and 1.

Those may be acceptable results for some circumstances, but it probably
isn't for others.

The problem, I think, is intractable. What is *exactly* 12/30/2004 plus
two months? Legitimate cases can be made for any day in the range
2/27/2005 - 3/2/2005, depending on how you define "month".
 
G

Guest

Once the period of time between the two dates is calculated, is there any way
to allocate a certian amount of money over that period in terms of straight
line depreciation. For example:

If the cost of an asset is $1000 and it is depreciated over a 10 year
period, the asset would be depreciated $100/year
($1,000/10years=$100/year;salvage value being ignored).

So basically...once the annual depreciation is determined ($100 for the
above example), can I take the DATEDIF cell and multiply it by the annual
depreciation for the asset...taking into account years, months, and days?

Thanks for any help.
Peter
 
J

JE McGimpsey

That would be a rather backward way of doing things, since your DATEDIF
functions were being concatenated into a text string.

If A1 is your inservice date, and B1 is your period date, and you really
need exact daily depreciation (though I wouldn't know why), I'd be more
inclined to use

=(B1-A1)*SLN(1000,0,DATE(YEAR(A1)+10,MONTH(A1),DAY(A1))-A1)

(using SLN allows you to take salvage into account). You'd be a little
less accurate with

=(B1-A1)*SLN(1000, 0, 3652.5)

depending on the timing of leap years - you may be off by a dime or so
at any one point.
 
M

Mangesh

=YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1) & " " &
(MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12,0)
& " " &
A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1)),MONTH(A1)
+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12,0
)),DAY(A1)))+IF(A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1)
,0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2
)<MONTH(A1),12,0)),DAY(A1)))<0,DAY(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH
(A2)>=MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0
,1)+IF(MONTH(A2)<MONTH(A1),12,0)),DAY(A1))),0)


- Mangesh
 
J

JE McGimpsey

Hmmmm...

A1: 12/30/2004

A2: 3/1/2005 ===> 0 2 1

Add one day:

A2: 3/2/2005 ===> 0 2 0

Add another day:

A2: 3/3/2005 ===> 0 2 1

or

A2: 12/1/2005 ===> 1 -1 1
 
M

Mangesh

=YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1) & " " &
(MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12,0)
& " " &
IF(DAY((DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1)),MONTH
(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),
12,0)),DAY(A1))))=DAY(A1),A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>
=MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+I
F(MONTH(A2)<MONTH(A1),12,0)),DAY(A1))),DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-D
AY(A1)+DAY(A2))

should solve that problem.

Mangesh
 
M

Mangesh

1 problem - month still left

Mangesh


Mangesh said:
=YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1) & " " &
(MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12,0)
& " " &
IF(DAY((DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1)),MONTH
(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),
12,0)),DAY(A1))))=DAY(A1),A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>
=MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+I
F(MONTH(A2)<MONTH(A1),12,0)),DAY(A1))),DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-D
AY(A1)+DAY(A2))

should solve that problem.

Mangesh





(MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12,0)
A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)>=MONTH(A1),0,1)),MONTH(A1)
+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12,0
,0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0,1)+IF(MONTH(A2
(A2)>=MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)>=DAY(A1),0
Click to expand...
 
M

Mangesh

This should solve the negative month problem as well.

=YEAR(M2)-YEAR(M1)-IF(MONTH(M2)>MONTH(M1),0,1) & " " &
(MONTH(M2)-MONTH(M1))-IF(DAY(M2)>=DAY(M1),0,1)+IF(MONTH(M2)<=MONTH(M1),12,0)
& " " &
IF(DAY((DATE(YEAR(M1)+(YEAR(M2)-YEAR(M1)-IF(MONTH(M2)>=MONTH(M1),0,1)),MONTH
(M1)+((MONTH(M2)-MONTH(M1))-IF(DAY(M2)>=DAY(M1),0,1)+IF(MONTH(M2)<MONTH(M1),
12,0)),DAY(M1))))=DAY(M1),M2-(DATE(YEAR(M1)+(YEAR(M2)-YEAR(M1)-IF(MONTH(M2)>
=MONTH(M1),0,1)),MONTH(M1)+((MONTH(M2)-MONTH(M1))-IF(DAY(M2)>=DAY(M1),0,1)+I
F(MONTH(M2)<MONTH(M1),12,0)),DAY(M1))),DAY(DATE(YEAR(M1),MONTH(M1)+1,1)-1)-D
AY(M1)+DAY(M2))


- Mangesh
 
R

Ron Rosenfeld

This should solve the negative month problem as well.

=YEAR(M2)-YEAR(M1)-IF(MONTH(M2)>MONTH(M1),0,1) & " " &
(MONTH(M2)-MONTH(M1))-IF(DAY(M2)>=DAY(M1),0,1)+IF(MONTH(M2)<=MONTH(M1),12,0)
& " " &
IF(DAY((DATE(YEAR(M1)+(YEAR(M2)-YEAR(M1)-IF(MONTH(M2)>=MONTH(M1),0,1)),MONTH
(M1)+((MONTH(M2)-MONTH(M1))-IF(DAY(M2)>=DAY(M1),0,1)+IF(MONTH(M2)<MONTH(M1),
12,0)),DAY(M1))))=DAY(M1),M2-(DATE(YEAR(M1)+(YEAR(M2)-YEAR(M1)-IF(MONTH(M2)>
=MONTH(M1),0,1)),MONTH(M1)+((MONTH(M2)-MONTH(M1))-IF(DAY(M2)>=DAY(M1),0,1)+I
F(MONTH(M2)<MONTH(M1),12,0)),DAY(M1))),DAY(DATE(YEAR(M1),MONTH(M1)+1,1)-1)-D
AY(M1)+DAY(M2))
Click to expand...

It may solve the negative month problem, but given:

28 Feb 2005
1 Jan 2006

I would count 10 full months (Mar-Dec) plus one or two days (depending on
whether one is including or excluding the bounds. Your formula gives a result
of 10 months 4 days.

Also:

1 Jan 2005
2 Jan 2005

Your formula gives: -1 12 1


But consider also the issue of what should be the answer given dates:

27 Feb 2005
28 Mar 2005

Is the "best" answer 1 month, 27 days, or 28 days?

My point remains that dealing with months as a time interval is inherently
confusing, unless the rules for using months are very clearly stated. And even
then there will be some results that can only be explained by clearly
understanding the stated rules.


--ron
 
M

Mangesh

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:
28 Feb 2005 to 1 Jan 2006
27 Feb 2005 to 28 Mar 2005
Click to expand...

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....

Regards
Mangesh
 
R

Ron Rosenfeld

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....
Click to expand...

Your formula does seem to follow your rules.

But those are not rules that I would use <g>.

They give rise to the (to me) illogical results with regard to the month of
February.

For example: 31 Jan 05 -- 28 Feb 05 to me encompasses a full month; as does 28
Feb 05 -- 31 Mar 05 (not counting Day 1). Yet the first gives a result of 0 0
28; and the second 0 1 3.

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

If I were using "months" in a count, I think I would devise an algorithm that
would count full calendar months; followed by the number of days that is
outside that range. So both of my examples above would result in "1 month".

I would either accept that the days out of range might total to more than 31,
or arbitrarily set 30 as the number of days in that pseudo-month.

Another alternative would be to arbitrarily assume 30 day months and 360 day
years. Which is what financial institutions did, and some probably still do.

And I'm sure others would find illogical results with my "rules" :)




--ron
 

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

Similar Threads

working with dates in excel 3
Populate days of the month in a column by checking on date 4
date calculation 2
Using date function in an if statement 2
My discovery on adding months and days to a date! 8
Re: How can I count the number of SATURDAYS in a month? 0
Multiple date calculations 2
No of Days between two dates and offset formula 4

Top