Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bluenose
 
Posts: n/a
Default Number of years, months, days between two dates.

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)<M ONTH(A1),DAY(B1)
=DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)<D AY(A1)),-1)))&" months, "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days"


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
  #2   Report Post  
PC
 
Posts: n/a
Default

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" wrote in message
...
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)<M ONTH(A1),DAY(B1)
=DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)<D AY(A1)),-1)))&" months,

"&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days"

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



  #3   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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.


In article ,
"PC" wrote:

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

  #4   Report Post  
PC
 
Posts: n/a
Default

Thanks JE

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

PC




"JE McGimpsey" wrote in message
...
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.


In article ,
"PC" wrote:

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



  #5   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Thu, 19 May 2005 15:57:03 -0700, "Bluenose"
wrote:

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"


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


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


  #6   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

Hi


"JE McGimpsey" wrote in message
...
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.



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(E OMONTH(A1,0))-DAY(EOMONTH(B1,-1)))

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )





In article ,
"PC" wrote:

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



  #7   Report Post  
Mangesh
 
Posts: n/a
Default

=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




"Ron Rosenfeld" wrote in message
...
On Thu, 19 May 2005 15:57:03 -0700, "Bluenose"
wrote:

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"

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


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



  #8   Report Post  
Mangesh
 
Posts: n/a
Default

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

- Mangesh



"Bluenose" wrote in message
...
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)<M ONTH(A1),DAY(B1)
=DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)<D AY(A1)),-1)))&" months,

"&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days"

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



  #9   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Fri, 20 May 2005 11:44:13 +0530, "Mangesh"
wrote:

=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


It gives silly answer with

A1: 31 Jan 2005
A2: 1 Mar 2005
--ron
  #10   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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


In article ,
"Arvi Laanemets" wrote:

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(E OMONTH(A1,0))-DAY(EOMONTH(B1
,-1)))



  #11   Report Post  
Peter Burkes
 
Posts: n/a
Default

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

"Arvi Laanemets" wrote:

Hi


"JE McGimpsey" wrote in message
...
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.



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(E OMONTH(A1,0))-DAY(EOMONTH(B1,-1)))

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )





In article ,
"PC" wrote:

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




  #12   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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.


In article ,
Peter Burkes <Peter wrote:

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?

  #13   Report Post  
Mangesh
 
Posts: n/a
Default

=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



"Ron Rosenfeld" wrote in message
...
On Fri, 20 May 2005 11:44:13 +0530, "Mangesh"


wrote:

=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)),DA

Y
(A2)-DAY(A1))

A1 = start date
A2 = End date


It gives silly answer with

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



  #14   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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





In article ,
"Mangesh" wrote:

=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)

  #15   Report Post  
Mangesh
 
Posts: n/a
Default

=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(YE AR(A1),MONTH(A1)+1,1)-1)-D
AY(A1)+DAY(A2))

should solve that problem.

Mangesh




"JE McGimpsey" wrote in message
...
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





In article ,
"Mangesh" wrote:

=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(A

1)

,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(MON

TH

(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)





  #16   Report Post  
Mangesh
 
Posts: n/a
Default

1 problem - month still left

Mangesh


"Mangesh" wrote in message
...
=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(YE AR(A1),MONTH(A1)+1,1)-1)-D
AY(A1)+DAY(A2))

should solve that problem.

Mangesh




"JE McGimpsey" wrote in message
...
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





In article ,
"Mangesh" wrote:

=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(A
1)


,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(MON
TH


(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)





  #17   Report Post  
Mangesh
 
Posts: n/a
Default

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),1 2,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(YE AR(M1),MONTH(M1)+1,1)-1)-D
AY(M1)+DAY(M2))


- Mangesh




"Mangesh" wrote in message
...
=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(YE AR(A1),MONTH(A1)+1,1)-1)-D
AY(A1)+DAY(A2))

should solve that problem.

Mangesh




"JE McGimpsey" wrote in message
...
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





In article ,
"Mangesh" wrote:

=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(A
1)


,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(MON
TH


(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)





  #18   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Sat, 21 May 2005 11:50:31 +0530, "Mangesh"
wrote:

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),1 2,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(Y EAR(M1),MONTH(M1)+1,1)-1)-D
AY(M1)+DAY(M2))


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
  #19   Report Post  
Mangesh
 
Posts: n/a
Default

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),AN D(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)+I F(AN
D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<=D AY(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


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



"Ron Rosenfeld" wrote in message
...
On Sat, 21 May 2005 11:50:31 +0530, "Mangesh"


wrote:

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),1 2,0

)
& " " &


IF(DAY((DATE(YEAR(M1)+(YEAR(M2)-YEAR(M1)-IF(MONTH(M2)=MONTH(M1),0,1)),MONT

H

(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(Y EAR(M1),MONTH(M1)+1,1)-1)-

D
AY(M1)+DAY(M2))


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



  #20   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Mon, 23 May 2005 12:15:21 +0530, "Mangesh"
wrote:

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


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


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


  #21   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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?







In article ,
"Mangesh" wrote:

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),AN D(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)+I F(AN
D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<=D AY(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


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

  #22   Report Post  
Mangesh
 
Posts: n/a
Default

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),AN D(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)+I F(AN
D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<=D AY(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




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



  #23   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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


In article ,
"Mangesh" wrote:

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),AN D(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)+I F(AN
D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<=D AY(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.

  #24   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Mon, 23 May 2005 12:15:21 +0530, "Mangesh"
wrote:

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),AN D(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)+I F(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


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
  #25   Report Post  
Mangesh
 
Posts: n/a
Default

Hi JE, Ron,


Ok. another minor adjustment:

=(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)MONTH(B1),AN D(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)+I F(AN
D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<=D AY(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(Y EAR(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




"JE McGimpsey" wrote in message
...
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





  #26   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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




In article ,
"Mangesh" wrote:

Hi JE, Ron,


Ok. another minor adjustment:

=(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)MONTH(B1),AN D(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)+I F(AN
D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<=D AY(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(Y EAR(A1),MONTH(A1)+1,1)-1)-
DAY(A1),0)

  #27   Report Post  
Mangesh
 
Posts: n/a
Default

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



"JE McGimpsey" wrote in message
...
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




In article ,
"Mangesh" wrote:

Hi JE, Ron,


Ok. another minor adjustment:


=(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)MONTH(B1),AN D(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)+I F(AN

D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<=D AY(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(Y EAR(A1),MONTH(A1)+1,1)-1

)-
DAY(A1),0)



  #28   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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


In article ,
"Mangesh" wrote:

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



"JE McGimpsey" wrote in message
...
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

  #29   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Tue, 24 May 2005 11:14:03 +0530, "Mangesh"
wrote:

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
  #30   Report Post  
Mangesh
 
Posts: n/a
Default

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



"Ron Rosenfeld" wrote in message
...
On Tue, 24 May 2005 11:14:03 +0530, "Mangesh"


wrote:

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





  #31   Report Post  
Mangesh Yadav
 
Posts: n/a
Default

Hi JE,

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

Mangesh



"JE McGimpsey" wrote in message
...
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


In article ,
"Mangesh" wrote:

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



"JE McGimpsey" wrote in message
...
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



  #32   Report Post  
Jesline
 
Posts: n/a
Default


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?




"PC" wrote:

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" wrote in message
...
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)<M ONTH(A1),DAY(B1)
=DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)<D AY(A1)),-1)))&" months,

"&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days"

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




  #33   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
wrote:


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
  #34   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"Jesline" wrote in message
...

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?




"PC" wrote:

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" wrote in message
...
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)<M ONTH(A1),DAY(B1)
=DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)<D AY(A1)),-1)))&"
months,

"&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days"

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






  #35   Report Post  
Grizzly Bear
 
Posts: n/a
Default


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


--
Grizzly Bear


------------------------------------------------------------------------
Grizzly Bear's Profile: http://www.excelforum.com/member.php...fo&userid=1838
View this thread: http://www.excelforum.com/showthread...hreadid=372455

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
working with dates in fiscal years justin_vasko Excel Worksheet Functions 3 May 18th 05 08:39 PM
how to calculate years, months by using one formula srinivasa rao Charts and Charting in Excel 2 April 15th 05 08:28 AM
Calculating interest on number of days in the period Ron Excel Worksheet Functions 0 January 18th 05 01:59 AM
Calculate months and years Ned Ludd Excel Discussion (Misc queries) 5 December 10th 04 02:57 PM
How do I display months and years between two dates JSmith Excel Discussion (Misc queries) 1 November 30th 04 05:41 PM


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

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

About Us

"It's about Microsoft Excel"