Number of years, months, days between two dates.

[Bluenose]

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

[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" <[email protected]> wrote in message
news:[email protected]...
> 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

[PC]

Thanks JE

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

PC




"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> "PC" <[email protected]> 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

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


In article <[email protected]>,
"PC" <[email protected]> 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

[Ron Rosenfeld]

On Thu, 19 May 2005 15:57:03 -0700, "Bluenose"
<[email protected]> 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

[Arvi Laanemets]

Hi


"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> 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(EOMONTH(A1,0))-DAY(EOMONTH(B1,-1)))

--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )



>
>
> In article <[email protected]>,
> "PC" <[email protected]> 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

[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




"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Thu, 19 May 2005 15:57:03 -0700, "Bluenose"
> <[email protected]> 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

[Mangesh]

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

- Mangesh



"Bluenose" <[email protected]> wrote in message
news:[email protected]...
> 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

[Ron Rosenfeld]

On Fri, 20 May 2005 11:44:13 +0530, "Mangesh" <[email protected]>
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

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


In article <[email protected]>,
"Arvi Laanemets" <[email protected]> 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(EOMONTH(A1,0))-DAY(EOMONTH(B1
> ,-1)))

[Peter Burkes]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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(EOMONTH(A1,0))-DAY(EOMONTH(B1,-1)))
>
> --
> Arvi Laanemets
> ( My real mail address: arvil<at>tarkon.ee )
>
>
>
> >
> >
> > In article <[email protected]>,
> > "PC" <[email protected]> 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
>
>
>

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


In article <[email protected]>,
Peter Burkes <Peter [email protected]> 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?

[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



"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Fri, 20 May 2005 11:44:13 +0530, "Mangesh"
<[email protected]>
> 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

[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





In article <[email protected]>,
"Mangesh" <[email protected]> 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)

[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




"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> "Mangesh" <[email protected]> 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)

[Mangesh]

1 problem - month still left

Mangesh


"Mangesh" <[email protected]> wrote in message
news:#[email protected]...
> =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
>
>
>
>
> "JE McGimpsey" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <[email protected]>,
> > "Mangesh" <[email protected]> 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)
>
>

[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




"Mangesh" <[email protected]> wrote in message
news:#[email protected]...
> =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
>
>
>
>
> "JE McGimpsey" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <[email protected]>,
> > "Mangesh" <[email protected]> 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)
>
>

[Ron Rosenfeld]

On Sat, 21 May 2005 11:50:31 +0530, "Mangesh" <[email protected]>
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),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))
>

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

[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

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" <[email protected]> wrote in message
news:[email protected]...
> On Sat, 21 May 2005 11:50:31 +0530, "Mangesh"
<[email protected]>
> 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),12,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(YEAR(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

[Ron Rosenfeld]

On Mon, 23 May 2005 12:15:21 +0530, "Mangesh" <[email protected]>
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

[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?







In article <#[email protected]>,
"Mangesh" <[email protected]> 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),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
>
> 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....

[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




> 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

[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


In article <eYrLq#[email protected]>,
"Mangesh" <[email protected]> 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),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.

[Ron Rosenfeld]

On Mon, 23 May 2005 12:15:21 +0530, "Mangesh" <[email protected]>
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),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
>
>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

[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




"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> 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

[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




In article <#[email protected]>,
"Mangesh" <[email protected]> wrote:

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

[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



"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> 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 <#[email protected]>,
> "Mangesh" <[email protected]> wrote:
>
> > 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)

[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


In article <O#[email protected]>,
"Mangesh" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >

[Ron Rosenfeld]

On Tue, 24 May 2005 11:14:03 +0530, "Mangesh" <[email protected]>
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

[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



"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Tue, 24 May 2005 11:14:03 +0530, "Mangesh"
<[email protected]>
> 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

[Mangesh Yadav]

Hi JE,

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

Mangesh



"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> 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 <O#[email protected]>,
> "Mangesh" <[email protected]> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >

[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?




"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Ron Rosenfeld]

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<[email protected]> 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

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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"Jesline" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

[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

[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?




"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"Jesline" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

[Ron Rosenfeld]

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<[email protected]> 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

[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?




"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Ron Rosenfeld]

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<[email protected]> 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

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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"Jesline" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

[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?




"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Ron Rosenfeld]

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<[email protected]> 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

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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"Jesline" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

[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?




"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Ron Rosenfeld]

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<[email protected]> 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

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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"Jesline" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"Jesline" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

[Ron Rosenfeld]

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<[email protected]> 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

[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?




"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"Jesline" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

[Ron Rosenfeld]

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<[email protected]> 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

[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?




"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"Jesline" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

[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?




"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Ron Rosenfeld]

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<[email protected]> 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

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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"Jesline" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

[Ron Rosenfeld]

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<[email protected]> 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

[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?




"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Ron Rosenfeld]

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<[email protected]> 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

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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"Jesline" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

[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?




"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[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?




"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Ron Rosenfeld]

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<[email protected]> 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

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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"Jesline" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

[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?




"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Ron Rosenfeld]

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<[email protected]> 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

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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"Jesline" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

[gunsmokegirl]

I'm looking for information on this topic-- how to automatically count the number of days between two dates. Seems very cumbersome. =( Anyone know of a website that gives you the option to plug in two dates and calculate the days between?

[NBVC]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.