Number of years, months, days between two dates.

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

=YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1),
DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1)
<=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<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

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" <Bluenose@discussions.microsoft.com> wrote in message
news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
> 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

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 <O5GIPhMXFHA.2256@TK2MSFTNGP14.phx.gbl>,
"PC" <pcorrado@msn.com> 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

Thanks JE

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

PC




"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-09C7D7.17273519052005@msnews.microsoft.com...
> 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 <O5GIPhMXFHA.2256@TK2MSFTNGP14.phx.gbl>,
> "PC" <pcorrado@msn.com> 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

Hi


"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-09C7D7.17273519052005@msnews.microsoft.com...
> 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 <O5GIPhMXFHA.2256@TK2MSFTNGP14.phx.gbl>,
> "PC" <pcorrado@msn.com> 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

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 <OEEC36PXFHA.2740@TK2MSFTNGP14.phx.gbl>,
"Arvi Laanemets" <garbage@hot.ee> 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)))

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" <jemcgimpsey@mvps.org> wrote in message
> news:jemcgimpsey-09C7D7.17273519052005@msnews.microsoft.com...
> > 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 <O5GIPhMXFHA.2256@TK2MSFTNGP14.phx.gbl>,
> > "PC" <pcorrado@msn.com> 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
>
>
>

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 <EBB0D4B5-5550-468F-90E9-1B45956CA22C@microsoft.com>,
Peter Burkes <Peter Burkes@discussions.microsoft.com> 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?

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" <Bluenose@discussions.microsoft.com> wrote in message
> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
> > 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
>
>
>

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<Jesline@discussions.microsoft.com> 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

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" <Jesline@discussions.microsoft.com> wrote in message
news:C122443D-2D35-4CD4-88FA-854E3E34A56E@microsoft.com...
>
> 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" <Bluenose@discussions.microsoft.com> wrote in message
>> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
>> > 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
>>
>>
>>

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" <Bluenose@discussions.microsoft.com> wrote in message
> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
> > 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
>
>
>

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" <Bluenose@discussions.microsoft.com> wrote in message
> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
> > 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
>
>
>

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<Jesline@discussions.microsoft.com> 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

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<Jesline@discussions.microsoft.com> 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

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" <Jesline@discussions.microsoft.com> wrote in message
news:C122443D-2D35-4CD4-88FA-854E3E34A56E@microsoft.com...
>
> 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" <Bluenose@discussions.microsoft.com> wrote in message
>> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
>> > 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
>>
>>
>>

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" <Jesline@discussions.microsoft.com> wrote in message
news:C122443D-2D35-4CD4-88FA-854E3E34A56E@microsoft.com...
>
> 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" <Bluenose@discussions.microsoft.com> wrote in message
>> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
>> > 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
>>
>>
>>

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<Jesline@discussions.microsoft.com> 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

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" <Jesline@discussions.microsoft.com> wrote in message
news:C122443D-2D35-4CD4-88FA-854E3E34A56E@microsoft.com...
>
> 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" <Bluenose@discussions.microsoft.com> wrote in message
>> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
>> > 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
>>
>>
>>

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" <Bluenose@discussions.microsoft.com> wrote in message
> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
> > 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
>
>
>

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" <Bluenose@discussions.microsoft.com> wrote in message
> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
> > 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
>
>
>

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" <Bluenose@discussions.microsoft.com> wrote in message
> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
> > 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
>
>
>

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<Jesline@discussions.microsoft.com> 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

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<Jesline@discussions.microsoft.com> 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

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" <Jesline@discussions.microsoft.com> wrote in message
news:C122443D-2D35-4CD4-88FA-854E3E34A56E@microsoft.com...
>
> 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" <Bluenose@discussions.microsoft.com> wrote in message
>> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
>> > 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
>>
>>
>>

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" <Jesline@discussions.microsoft.com> wrote in message
news:C122443D-2D35-4CD4-88FA-854E3E34A56E@microsoft.com...
>
> 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" <Bluenose@discussions.microsoft.com> wrote in message
>> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
>> > 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
>>
>>
>>

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" <Bluenose@discussions.microsoft.com> wrote in message
> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
> > 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
>
>
>

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" <Bluenose@discussions.microsoft.com> wrote in message
> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
> > 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
>
>
>

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<Jesline@discussions.microsoft.com> 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

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<Jesline@discussions.microsoft.com> 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

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" <Jesline@discussions.microsoft.com> wrote in message
news:C122443D-2D35-4CD4-88FA-854E3E34A56E@microsoft.com...
>
> 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" <Bluenose@discussions.microsoft.com> wrote in message
>> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
>> > 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
>>
>>
>>

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" <Jesline@discussions.microsoft.com> wrote in message
news:C122443D-2D35-4CD4-88FA-854E3E34A56E@microsoft.com...
>
> 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" <Bluenose@discussions.microsoft.com> wrote in message
>> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
>> > 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
>>
>>
>>

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<Jesline@discussions.microsoft.com> 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

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" <Jesline@discussions.microsoft.com> wrote in message
news:C122443D-2D35-4CD4-88FA-854E3E34A56E@microsoft.com...
>
> 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" <Bluenose@discussions.microsoft.com> wrote in message
>> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
>> > 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
>>
>>
>>

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" <Bluenose@discussions.microsoft.com> wrote in message
> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
> > 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
>
>
>

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" <Bluenose@discussions.microsoft.com> wrote in message
> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
> > 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
>
>
>

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" <Bluenose@discussions.microsoft.com> wrote in message
> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
> > 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
>
>
>

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" <Bluenose@discussions.microsoft.com> wrote in message
> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
> > 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
>
>
>

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<Jesline@discussions.microsoft.com> 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

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<Jesline@discussions.microsoft.com> 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

On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
<Jesline@discussions.microsoft.com> 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

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" <Jesline@discussions.microsoft.com> wrote in message
news:C122443D-2D35-4CD4-88FA-854E3E34A56E@microsoft.com...
>
> 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" <Bluenose@discussions.microsoft.com> wrote in message
>> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
>> > 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
>>
>>
>>

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" <Jesline@discussions.microsoft.com> wrote in message
news:C122443D-2D35-4CD4-88FA-854E3E34A56E@microsoft.com...
>
> 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" <Bluenose@discussions.microsoft.com> wrote in message
>> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
>> > 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
>>
>>
>>

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" <Jesline@discussions.microsoft.com> wrote in message
news:C122443D-2D35-4CD4-88FA-854E3E34A56E@microsoft.com...
>
> 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" <Bluenose@discussions.microsoft.com> wrote in message
>> news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
>> > 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
>>
>>
>>

On Thu, 19 May 2005 15:57:03 -0700, "Bluenose"
<Bluenose@discussions.microsoft.com> 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

=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" <ronrosenfeld@nospam.org> wrote in message
news:rocq819b00t3a6iestjc2avk1o6ihaui6v@4ax.com...
> On Thu, 19 May 2005 15:57:03 -0700, "Bluenose"
> <Bluenose@discussions.microsoft.com> 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

On Fri, 20 May 2005 11:44:13 +0530, "Mangesh" <mangesh.yadav@NOSPAMgmail.com>
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

=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" <ronrosenfeld@nospam.org> wrote in message
news:8acr81p00i8i2au0hgne8d3kn6lupc5e5u@4ax.com...
> On Fri, 20 May 2005 11:44:13 +0530, "Mangesh"
<mangesh.yadav@NOSPAMgmail.com>
> 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

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 <O8A3mLcXFHA.4032@tk2msftngp13.phx.gbl>,
"Mangesh" <mangesh.yadav@NOSPAMgmail.com> 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)

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

- Mangesh



"Bluenose" <Bluenose@discussions.microsoft.com> wrote in message
news:3B15AB91-BC5B-4ACF-B532-458A29AF780F@microsoft.com...
> 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

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?