Calculating days in a month

STFC · 01-26-2005, 06:06 AM

Hello,

Hope someone can help.

A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th April

C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until Dec.

Therefore for the above example C1 would = 31days D1 = 28days E1 = 31days
and F1 = 4 days.

I have been playing around with IF and DATEDIF but I'm getting nowhere. Any
help would be much appreciated.

Bob Phillips · 01-26-2005, 07:06 AM

C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
--

HTH

RP
(remove nothere from the email address if mailing direct)

"STFC" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> Hope someone can help.
>
> A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th April
>
> C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until Dec.
>
> Therefore for the above example C1 would = 31days D1 = 28days E1 = 31days
> and F1 = 4 days.
>
> I have been playing around with IF and DATEDIF but I'm getting nowhere.
Any
> help would be much appreciated.

Stefi · 01-26-2005, 07:06 AM

Format Cell C1 as dd and the formula: =EOMONTH(A1;0)

Stefi

Ã¢â‚¬Å¾STFCÃ¢â‚¬? ezt ÃƒÂ*rta:

> Hello,
>
> Hope someone can help.
>
> A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th April
>
> C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until Dec.
>
> Therefore for the above example C1 would = 31days D1 = 28days E1 = 31days
> and F1 = 4 days.
>
> I have been playing around with IF and DATEDIF but I'm getting nowhere. Any
> help would be much appreciated.

STFC · 01-26-2005, 07:06 AM

Hi Bob

Wow, thanks for the quick response.

I was really after a formaula that would take into account partial months.

Therefore if Start Date = 15 Jan, End Date= 4th Apr

Jan=17 days, Feb=28days Mar= 31day and Apr=4 days, May=0, Jun=0 etc. This is
for when campaigns ran in a year, and which months it ran in.

Andy.

"Bob Phillips" wrote:

> C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "STFC" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > Hope someone can help.
> >
> > A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th April
> >
> > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until Dec.
> >
> > Therefore for the above example C1 would = 31days D1 = 28days E1 = 31days
> > and F1 = 4 days.
> >
> > I have been playing around with IF and DATEDIF but I'm getting nowhere.
> Any
> > help would be much appreciated.
>
>
>

Bob Phillips · 01-26-2005, 08:06 AM

Are you looking for a more variable formula, or will the start and end dates
always span 4 months?

--

HTH

RP
(remove nothere from the email address if mailing direct)

"STFC" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob
>
> Wow, thanks for the quick response.
>
> I was really after a formaula that would take into account partial months.
>
> Therefore if Start Date = 15 Jan, End Date= 4th Apr
>
> Jan=17 days, Feb=28days Mar= 31day and Apr=4 days, May=0, Jun=0 etc. This
is
> for when campaigns ran in a year, and which months it ran in.
>
> Andy.
>
>
>
> "Bob Phillips" wrote:
>
> > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "STFC" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hello,
> > >
> > > Hope someone can help.
> > >
> > > A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th April
> > >
> > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until Dec.
> > >
> > > Therefore for the above example C1 would = 31days D1 = 28days E1 =
31days
> > > and F1 = 4 days.
> > >
> > > I have been playing around with IF and DATEDIF but I'm getting
nowhere.
> > Any
> > > help would be much appreciated.
> >
> >
> >

STFC · 01-26-2005, 08:06 AM

Sorry I haven't been that clear have I.

Yes a more variable formula - the campaigns can run for anything from a week
to a year.

"Bob Phillips" wrote:

> Are you looking for a more variable formula, or will the start and end dates
> always span 4 months?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "STFC" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob
> >
> > Wow, thanks for the quick response.
> >
> > I was really after a formaula that would take into account partial months.
> >
> > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> >
> > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days, May=0, Jun=0 etc. This
> is
> > for when campaigns ran in a year, and which months it ran in.
> >
> > Andy.
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "STFC" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hello,
> > > >
> > > > Hope someone can help.
> > > >
> > > > A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th April
> > > >
> > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until Dec.
> > > >
> > > > Therefore for the above example C1 would = 31days D1 = 28days E1 =
> 31days
> > > > and F1 = 4 days.
> > > >
> > > > I have been playing around with IF and DATEDIF but I'm getting
> nowhere.
> > > Any
> > > > help would be much appreciated.
> > >
> > >
> > >
>
>
>

Bob Phillips · 01-26-2005, 09:06 AM

Okay, another shot.

Assuming that the start date is in A1, end date is in B1, and the campaign
days are in C1:C12

In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
Select all C2:C11 cells together, then enter this array formula into the
formula bar (commit with Ctrlo-Shift-Enter)
=IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))

--

HTH

RP
(remove nothere from the email address if mailing direct)

"STFC" <[email protected]> wrote in message
news:[email protected]...
> Sorry I haven't been that clear have I.
>
> Yes a more variable formula - the campaigns can run for anything from a
week
> to a year.
>
> "Bob Phillips" wrote:
>
> > Are you looking for a more variable formula, or will the start and end
dates
> > always span 4 months?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "STFC" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Bob
> > >
> > > Wow, thanks for the quick response.
> > >
> > > I was really after a formaula that would take into account partial
months.
> > >
> > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> > >
> > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days, May=0, Jun=0 etc.
This
> > is
> > > for when campaigns ran in a year, and which months it ran in.
> > >
> > > Andy.
> > >
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "STFC" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hello,
> > > > >
> > > > > Hope someone can help.
> > > > >
> > > > > A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th April
> > > > >
> > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until Dec.
> > > > >
> > > > > Therefore for the above example C1 would = 31days D1 = 28days E1 =
> > 31days
> > > > > and F1 = 4 days.
> > > > >
> > > > > I have been playing around with IF and DATEDIF but I'm getting
> > nowhere.
> > > > Any
> > > > > help would be much appreciated.
> > > >
> > > >
> > > >
> >
> >
> >

Bob Phillips · 01-26-2005, 09:06 AM

You can actually do it all inj one formula,

Select C1:C12, and enter this formula, again as an array formula

=IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Okay, another shot.
>
> Assuming that the start date is in A1, end date is in B1, and the campaign
> days are in C1:C12
>
> In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
> Select all C2:C11 cells together, then enter this array formula into the
> formula bar (commit with Ctrlo-Shift-Enter)
>
=IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
> 1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "STFC" <[email protected]> wrote in message
> news:[email protected]...
> > Sorry I haven't been that clear have I.
> >
> > Yes a more variable formula - the campaigns can run for anything from a
> week
> > to a year.
> >
> > "Bob Phillips" wrote:
> >
> > > Are you looking for a more variable formula, or will the start and end
> dates
> > > always span 4 months?
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "STFC" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Bob
> > > >
> > > > Wow, thanks for the quick response.
> > > >
> > > > I was really after a formaula that would take into account partial
> months.
> > > >
> > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> > > >
> > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days, May=0, Jun=0 etc.
> This
> > > is
> > > > for when campaigns ran in a year, and which months it ran in.
> > > >
> > > > Andy.
> > > >
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "STFC" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Hello,
> > > > > >
> > > > > > Hope someone can help.
> > > > > >
> > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th April
> > > > > >
> > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until Dec.
> > > > > >
> > > > > > Therefore for the above example C1 would = 31days D1 = 28days E1
=
> > > 31days
> > > > > > and F1 = 4 days.
> > > > > >
> > > > > > I have been playing around with IF and DATEDIF but I'm getting
> > > nowhere.
> > > > > Any
> > > > > > help would be much appreciated.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>

Bob Phillips · 01-26-2005, 09:06 AM

Actually, this can be clarified as you are only running for up to one year
:-), and there is a problem with the formula. It works okay if the campaign
starts in Jan, but any other month fails.

Amendment #3.

=IF(ROW(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))=MONTH($A$1),DATE(YEAR(A$1)
,MONTH(A$1)+1,1)-A1,IF(ROW(INDIRECT("1:12"))=MONTH($B$1),DAY($B$1),DAY(DATE(
YEAR(A$1),MONTH(A$1)+ROW(INDIRECT("1:12")),0))*(MONTH($B$1)>=ROW(INDIRECT("1
:12")))))

and still an array formula

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Bob Phillips" <[email protected]> wrote in message
news:u1Y0k%[email protected]...
> You can actually do it all inj one formula,
>
> Select C1:C12, and enter this formula, again as an array formula
>
>
=IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
>
ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
> OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > Okay, another shot.
> >
> > Assuming that the start date is in A1, end date is in B1, and the
campaign
> > days are in C1:C12
> >
> > In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
> > Select all C2:C11 cells together, then enter this array formula into the
> > formula bar (commit with Ctrlo-Shift-Enter)
> >
>
=IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
> > 1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "STFC" <[email protected]> wrote in message
> > news:[email protected]...
> > > Sorry I haven't been that clear have I.
> > >
> > > Yes a more variable formula - the campaigns can run for anything from
a
> > week
> > > to a year.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Are you looking for a more variable formula, or will the start and
end
> > dates
> > > > always span 4 months?
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "STFC" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi Bob
> > > > >
> > > > > Wow, thanks for the quick response.
> > > > >
> > > > > I was really after a formaula that would take into account partial
> > months.
> > > > >
> > > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> > > > >
> > > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days, May=0, Jun=0
etc.
> > This
> > > > is
> > > > > for when campaigns ran in a year, and which months it ran in.
> > > > >
> > > > > Andy.
> > > > >
> > > > >
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "STFC" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Hello,
> > > > > > >
> > > > > > > Hope someone can help.
> > > > > > >
> > > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th April
> > > > > > >
> > > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until Dec.
> > > > > > >
> > > > > > > Therefore for the above example C1 would = 31days D1 = 28days
E1
> =
> > > > 31days
> > > > > > > and F1 = 4 days.
> > > > > > >
> > > > > > > I have been playing around with IF and DATEDIF but I'm getting
> > > > nowhere.
> > > > > > Any
> > > > > > > help would be much appreciated.
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
>
>

STFC · 01-26-2005, 09:06 AM

Hi Bob,

This is v impressive. I have some learning to do.

This is close to working, but if the start date = 2nd Feb then Jan should
have 0 days. As start dates and end dates could be any dates in the year.
Also a campaign could just run in 1 month eg 8th Jan - 15th Jan which =8
days.

Also is it possible to do the array where C1:N1 return the results.

I feel I'm being a bit cheeky now, but thanks for all the help.

"Bob Phillips" wrote:

> You can actually do it all inj one formula,
>
> Select C1:C12, and enter this formula, again as an array formula
>
> =IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
> ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
> OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > Okay, another shot.
> >
> > Assuming that the start date is in A1, end date is in B1, and the campaign
> > days are in C1:C12
> >
> > In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
> > Select all C2:C11 cells together, then enter this array formula into the
> > formula bar (commit with Ctrlo-Shift-Enter)
> >
> =IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
> > 1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "STFC" <[email protected]> wrote in message
> > news:[email protected]...
> > > Sorry I haven't been that clear have I.
> > >
> > > Yes a more variable formula - the campaigns can run for anything from a
> > week
> > > to a year.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Are you looking for a more variable formula, or will the start and end
> > dates
> > > > always span 4 months?
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "STFC" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi Bob
> > > > >
> > > > > Wow, thanks for the quick response.
> > > > >
> > > > > I was really after a formaula that would take into account partial
> > months.
> > > > >
> > > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> > > > >
> > > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days, May=0, Jun=0 etc.
> > This
> > > > is
> > > > > for when campaigns ran in a year, and which months it ran in.
> > > > >
> > > > > Andy.
> > > > >
> > > > >
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "STFC" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Hello,
> > > > > > >
> > > > > > > Hope someone can help.
> > > > > > >
> > > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th April
> > > > > > >
> > > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until Dec.
> > > > > > >
> > > > > > > Therefore for the above example C1 would = 31days D1 = 28days E1
> =
> > > > 31days
> > > > > > > and F1 = 4 days.
> > > > > > >
> > > > > > > I have been playing around with IF and DATEDIF but I'm getting
> > > > nowhere.
> > > > > > Any
> > > > > > > help would be much appreciated.
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
>
>
>

Bob Phillips · 01-26-2005, 09:06 AM

Can't resist another go, as it is still not right

=IF(ROW(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))=MONTH($A$1),DATE(YEAR(A$1)
,MONTH(A$1)+1,1)-A1,IF(ROW(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))=MONTH($
B$1)+(YEAR($B$1)-YEAR($A$1))*12,DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+ROW(
INDIRECT("1:12")),0))*(MONTH($B$1)+(YEAR($B$1)-YEAR($A$1))*12>=ROW(INDIRECT(
MONTH($A$1)&":"&MONTH($A$1)+11)))))

This works in all cases I can see except where the campaign starts and ends
in the same month. Getting a bit messy though.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Actually, this can be clarified as you are only running for up to one year
> :-), and there is a problem with the formula. It works okay if the
campaign
> starts in Jan, but any other month fails.
>
> Amendment #3.
>
>
=IF(ROW(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))=MONTH($A$1),DATE(YEAR(A$1)
>
,MONTH(A$1)+1,1)-A1,IF(ROW(INDIRECT("1:12"))=MONTH($B$1),DAY($B$1),DAY(DATE(
>
YEAR(A$1),MONTH(A$1)+ROW(INDIRECT("1:12")),0))*(MONTH($B$1)>=ROW(INDIRECT("1
> :12")))))
>
> and still an array formula
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:u1Y0k%[email protected]...
> > You can actually do it all inj one formula,
> >
> > Select C1:C12, and enter this formula, again as an array formula
> >
> >
>
=IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
> >
>
ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
> > OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:%[email protected]...
> > > Okay, another shot.
> > >
> > > Assuming that the start date is in A1, end date is in B1, and the
> campaign
> > > days are in C1:C12
> > >
> > > In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
> > > Select all C2:C11 cells together, then enter this array formula into
the
> > > formula bar (commit with Ctrlo-Shift-Enter)
> > >
> >
>
=IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
> > > 1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "STFC" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Sorry I haven't been that clear have I.
> > > >
> > > > Yes a more variable formula - the campaigns can run for anything
from
> a
> > > week
> > > > to a year.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Are you looking for a more variable formula, or will the start and
> end
> > > dates
> > > > > always span 4 months?
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "STFC" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Hi Bob
> > > > > >
> > > > > > Wow, thanks for the quick response.
> > > > > >
> > > > > > I was really after a formaula that would take into account
partial
> > > months.
> > > > > >
> > > > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> > > > > >
> > > > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days, May=0, Jun=0
> etc.
> > > This
> > > > > is
> > > > > > for when campaigns ran in a year, and which months it ran in.
> > > > > >
> > > > > > Andy.
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > > > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > > > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > > > > > > --
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > RP
> > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > >
> > > > > > >
> > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > Hello,
> > > > > > > >
> > > > > > > > Hope someone can help.
> > > > > > > >
> > > > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th April
> > > > > > > >
> > > > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until Dec.
> > > > > > > >
> > > > > > > > Therefore for the above example C1 would = 31days D1 =
28days
> E1
> > =
> > > > > 31days
> > > > > > > > and F1 = 4 days.
> > > > > > > >
> > > > > > > > I have been playing around with IF and DATEDIF but I'm
getting
> > > > > nowhere.
> > > > > > > Any
> > > > > > > > help would be much appreciated.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> >
> >
>
>

Bob Phillips · 01-26-2005, 10:06 AM

I have done it so that C1 contains the month the campaign starts in, so Jan
is in C12 (that is next year).

I spotted the same month problem in my last post, but it can be fixed, as
long as the campaign doesn't go over a year). To use C1:N1, use this formula

=IF((MONTH($A$1)=MONTH($B$1))*(MONTH($A$1)=COLUMN(INDIRECT(MONTH($A$1)&":"&M
ONTH($A$1)+11))),$B$1-$A$1,IF(COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11
))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(COLUMN(INDIRECT(MONTH($A
$1)&":"&MONTH($A$1)+11))=MONTH($B$1)+(YEAR($B$1)-YEAR($A$1))*12,DAY($B$1),DA
Y(DATE(YEAR(A$1),MONTH($A$1)+COLUMN(INDIRECT("1:12")),0))*(MONTH($B$1)+(YEAR
($B$1)-YEAR($A$1))*12>=COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))))))

--

HTH

RP
(remove nothere from the email address if mailing direct)

"STFC" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> This is v impressive. I have some learning to do.
>
> This is close to working, but if the start date = 2nd Feb then Jan should
> have 0 days. As start dates and end dates could be any dates in the year.
> Also a campaign could just run in 1 month eg 8th Jan - 15th Jan which =8
> days.
>
> Also is it possible to do the array where C1:N1 return the results.
>
> I feel I'm being a bit cheeky now, but thanks for all the help.
>
> "Bob Phillips" wrote:
>
> > You can actually do it all inj one formula,
> >
> > Select C1:C12, and enter this formula, again as an array formula
> >
> >
=IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
> >
ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
> > OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:%[email protected]...
> > > Okay, another shot.
> > >
> > > Assuming that the start date is in A1, end date is in B1, and the
campaign
> > > days are in C1:C12
> > >
> > > In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
> > > Select all C2:C11 cells together, then enter this array formula into
the
> > > formula bar (commit with Ctrlo-Shift-Enter)
> > >
> >
=IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
> > > 1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "STFC" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Sorry I haven't been that clear have I.
> > > >
> > > > Yes a more variable formula - the campaigns can run for anything
from a
> > > week
> > > > to a year.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Are you looking for a more variable formula, or will the start and
end
> > > dates
> > > > > always span 4 months?
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "STFC" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Hi Bob
> > > > > >
> > > > > > Wow, thanks for the quick response.
> > > > > >
> > > > > > I was really after a formaula that would take into account
partial
> > > months.
> > > > > >
> > > > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> > > > > >
> > > > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days, May=0, Jun=0
etc.
> > > This
> > > > > is
> > > > > > for when campaigns ran in a year, and which months it ran in.
> > > > > >
> > > > > > Andy.
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > > > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > > > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > > > > > > --
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > RP
> > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > >
> > > > > > >
> > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > Hello,
> > > > > > > >
> > > > > > > > Hope someone can help.
> > > > > > > >
> > > > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th April
> > > > > > > >
> > > > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until Dec.
> > > > > > > >
> > > > > > > > Therefore for the above example C1 would = 31days D1 =
28days E1
> > =
> > > > > 31days
> > > > > > > > and F1 = 4 days.
> > > > > > > >
> > > > > > > > I have been playing around with IF and DATEDIF but I'm
getting
> > > > > nowhere.
> > > > > > > Any
> > > > > > > > help would be much appreciated.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> >
> >
> >

STFC · 01-26-2005, 11:06 AM

Hi Bob,

First time using this site, will definitely use it again. I'm quite glad I
was right in thinking this wasn't easy to solve.

The formula is v v impressive. The only problem is trying to get a campaign
that starts in Mar to return 0 for Jan and Feb. Would it be easier to have 12
separate formulas for C1:N1 to induvidually workout if they had days running
in that month?

Thanks again for all your help, much appreciated.

"Bob Phillips" wrote:

> I have done it so that C1 contains the month the campaign starts in, so Jan
> is in C12 (that is next year).
>
> I spotted the same month problem in my last post, but it can be fixed, as
> long as the campaign doesn't go over a year). To use C1:N1, use this formula
>
> =IF((MONTH($A$1)=MONTH($B$1))*(MONTH($A$1)=COLUMN(INDIRECT(MONTH($A$1)&":"&M
> ONTH($A$1)+11))),$B$1-$A$1,IF(COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11
> ))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(COLUMN(INDIRECT(MONTH($A
> $1)&":"&MONTH($A$1)+11))=MONTH($B$1)+(YEAR($B$1)-YEAR($A$1))*12,DAY($B$1),DA
> Y(DATE(YEAR(A$1),MONTH($A$1)+COLUMN(INDIRECT("1:12")),0))*(MONTH($B$1)+(YEAR
> ($B$1)-YEAR($A$1))*12>=COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))))))
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "STFC" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob,
> >
> > This is v impressive. I have some learning to do.
> >
> > This is close to working, but if the start date = 2nd Feb then Jan should
> > have 0 days. As start dates and end dates could be any dates in the year.
> > Also a campaign could just run in 1 month eg 8th Jan - 15th Jan which =8
> > days.
> >
> > Also is it possible to do the array where C1:N1 return the results.
> >
> > I feel I'm being a bit cheeky now, but thanks for all the help.
> >
> > "Bob Phillips" wrote:
> >
> > > You can actually do it all inj one formula,
> > >
> > > Select C1:C12, and enter this formula, again as an array formula
> > >
> > >
> =IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
> > >
> ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
> > > OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Bob Phillips" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > Okay, another shot.
> > > >
> > > > Assuming that the start date is in A1, end date is in B1, and the
> campaign
> > > > days are in C1:C12
> > > >
> > > > In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
> > > > Select all C2:C11 cells together, then enter this array formula into
> the
> > > > formula bar (commit with Ctrlo-Shift-Enter)
> > > >
> > >
> =IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
> > > > 1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "STFC" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Sorry I haven't been that clear have I.
> > > > >
> > > > > Yes a more variable formula - the campaigns can run for anything
> from a
> > > > week
> > > > > to a year.
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > Are you looking for a more variable formula, or will the start and
> end
> > > > dates
> > > > > > always span 4 months?
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "STFC" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Hi Bob
> > > > > > >
> > > > > > > Wow, thanks for the quick response.
> > > > > > >
> > > > > > > I was really after a formaula that would take into account
> partial
> > > > months.
> > > > > > >
> > > > > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> > > > > > >
> > > > > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days, May=0, Jun=0
> etc.
> > > > This
> > > > > > is
> > > > > > > for when campaigns ran in a year, and which months it ran in.
> > > > > > >
> > > > > > > Andy.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Bob Phillips" wrote:
> > > > > > >
> > > > > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > > > > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > > > > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > > > > > > > --
> > > > > > > >
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > RP
> > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > >
> > > > > > > >
> > > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > > Hello,
> > > > > > > > >
> > > > > > > > > Hope someone can help.
> > > > > > > > >
> > > > > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th April
> > > > > > > > >
> > > > > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until Dec.
> > > > > > > > >
> > > > > > > > > Therefore for the above example C1 would = 31days D1 =
> 28days E1
> > > =
> > > > > > 31days
> > > > > > > > > and F1 = 4 days.
> > > > > > > > >
> > > > > > > > > I have been playing around with IF and DATEDIF but I'm
> getting
> > > > > > nowhere.
> > > > > > > > Any
> > > > > > > > > help would be much appreciated.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>

Bob Phillips · 01-26-2005, 11:06 AM

Hi STFC (sorry, don't know your name)

As long as the start date and end date are in the same year, and start month
will be less than end month, we can simplify the formula and get it the way
you want

=IF(AND(COLUMN(A1)>MONTH($A$1),COLUMN(A1)<MONTH($B$1)),DAY(DATE(YEAR($A$1),C
OLUMN(A1)+1,0)),IF(COLUMN(A1)=MONTH($A$1),DAY(DATE(YEAR($A$1),COLUMN(A1)+1,0
))-DAY($A$1),IF(COLUMN(A1)=MONTH($B$1),DAY($B$1),0)))

This is NOT an array formula, so just commit with Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"STFC" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> First time using this site, will definitely use it again. I'm quite glad I
> was right in thinking this wasn't easy to solve.
>
> The formula is v v impressive. The only problem is trying to get a
campaign
> that starts in Mar to return 0 for Jan and Feb. Would it be easier to have
12
> separate formulas for C1:N1 to induvidually workout if they had days
running
> in that month?
>
> Thanks again for all your help, much appreciated.
>
> "Bob Phillips" wrote:
>
> > I have done it so that C1 contains the month the campaign starts in, so
Jan
> > is in C12 (that is next year).
> >
> > I spotted the same month problem in my last post, but it can be fixed,
as
> > long as the campaign doesn't go over a year). To use C1:N1, use this
formula
> >
> >
=IF((MONTH($A$1)=MONTH($B$1))*(MONTH($A$1)=COLUMN(INDIRECT(MONTH($A$1)&":"&M
> >
ONTH($A$1)+11))),$B$1-$A$1,IF(COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11
>
> ))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(COLUMN(INDIRECT(MONTH(
$A
> >
$1)&":"&MONTH($A$1)+11))=MONTH($B$1)+(YEAR($B$1)-YEAR($A$1))*12,DAY($B$1),DA
> >
Y(DATE(YEAR(A$1),MONTH($A$1)+COLUMN(INDIRECT("1:12")),0))*(MONTH($B$1)+(YEAR
> >
($B$1)-YEAR($A$1))*12>=COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))))))
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "STFC" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Bob,
> > >
> > > This is v impressive. I have some learning to do.
> > >
> > > This is close to working, but if the start date = 2nd Feb then Jan
should
> > > have 0 days. As start dates and end dates could be any dates in the
year.
> > > Also a campaign could just run in 1 month eg 8th Jan - 15th Jan which
=8
> > > days.
> > >
> > > Also is it possible to do the array where C1:N1 return the results.
> > >
> > > I feel I'm being a bit cheeky now, but thanks for all the help.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > You can actually do it all inj one formula,
> > > >
> > > > Select C1:C12, and enter this formula, again as an array formula
> > > >
> > > >
> >
=IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
> > > >
> >
ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
> > > > OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Bob Phillips" <[email protected]> wrote in message
> > > > news:%[email protected]...
> > > > > Okay, another shot.
> > > > >
> > > > > Assuming that the start date is in A1, end date is in B1, and the
> > campaign
> > > > > days are in C1:C12
> > > > >
> > > > > In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
> > > > > Select all C2:C11 cells together, then enter this array formula
into
> > the
> > > > > formula bar (commit with Ctrlo-Shift-Enter)
> > > > >
> > > >
> >
=IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
> > > > >
1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "STFC" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Sorry I haven't been that clear have I.
> > > > > >
> > > > > > Yes a more variable formula - the campaigns can run for anything
> > from a
> > > > > week
> > > > > > to a year.
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > Are you looking for a more variable formula, or will the start
and
> > end
> > > > > dates
> > > > > > > always span 4 months?
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > RP
> > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > >
> > > > > > >
> > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > Hi Bob
> > > > > > > >
> > > > > > > > Wow, thanks for the quick response.
> > > > > > > >
> > > > > > > > I was really after a formaula that would take into account
> > partial
> > > > > months.
> > > > > > > >
> > > > > > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> > > > > > > >
> > > > > > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days, May=0,
Jun=0
> > etc.
> > > > > This
> > > > > > > is
> > > > > > > > for when campaigns ran in a year, and which months it ran
in.
> > > > > > > >
> > > > > > > > Andy.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "Bob Phillips" wrote:
> > > > > > > >
> > > > > > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > > > > > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > > > > > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > > > > > > > > --
> > > > > > > > >
> > > > > > > > > HTH
> > > > > > > > >
> > > > > > > > > RP
> > > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > Hello,
> > > > > > > > > >
> > > > > > > > > > Hope someone can help.
> > > > > > > > > >
> > > > > > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th
April
> > > > > > > > > >
> > > > > > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until
Dec.
> > > > > > > > > >
> > > > > > > > > > Therefore for the above example C1 would = 31days D1 =
> > 28days E1
> > > > =
> > > > > > > 31days
> > > > > > > > > > and F1 = 4 days.
> > > > > > > > > >
> > > > > > > > > > I have been playing around with IF and DATEDIF but I'm
> > getting
> > > > > > > nowhere.
> > > > > > > > > Any
> > > > > > > > > > help would be much appreciated.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Bob Phillips · 01-26-2005, 12:06 PM

Damn, forgot the dates in the same month again.

=IF(AND(MONTH($A$1)=MONTH($B$1),COLUMN(A1)=MONTH($A$1)),$B$1-$A$1,IF(AND(COL
UMN(A1)>MONTH($A$1),COLUMN(A1)<MONTH($B$1)),DAY(DATE(YEAR($A$1),COLUMN(A1)+1
,0)),IF(COLUMN(A1)=MONTH($A$1),DAY(DATE(YEAR($A$1),COLUMN(A1)+1,0))-DAY($A$1
),IF(COLUMN(A1)=MONTH($B$1),DAY($B$1),0))))

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Hi STFC (sorry, don't know your name)
>
> As long as the start date and end date are in the same year, and start
month
> will be less than end month, we can simplify the formula and get it the
way
> you want
>
>
=IF(AND(COLUMN(A1)>MONTH($A$1),COLUMN(A1)<MONTH($B$1)),DAY(DATE(YEAR($A$1),C
>
OLUMN(A1)+1,0)),IF(COLUMN(A1)=MONTH($A$1),DAY(DATE(YEAR($A$1),COLUMN(A1)+1,0
> ))-DAY($A$1),IF(COLUMN(A1)=MONTH($B$1),DAY($B$1),0)))
>
> This is NOT an array formula, so just commit with Enter.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "STFC" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob,
> >
> > First time using this site, will definitely use it again. I'm quite glad
I
> > was right in thinking this wasn't easy to solve.
> >
> > The formula is v v impressive. The only problem is trying to get a
> campaign
> > that starts in Mar to return 0 for Jan and Feb. Would it be easier to
have
> 12
> > separate formulas for C1:N1 to induvidually workout if they had days
> running
> > in that month?
> >
> > Thanks again for all your help, much appreciated.
> >
> > "Bob Phillips" wrote:
> >
> > > I have done it so that C1 contains the month the campaign starts in,
so
> Jan
> > > is in C12 (that is next year).
> > >
> > > I spotted the same month problem in my last post, but it can be fixed,
> as
> > > long as the campaign doesn't go over a year). To use C1:N1, use this
> formula
> > >
> > >
>
=IF((MONTH($A$1)=MONTH($B$1))*(MONTH($A$1)=COLUMN(INDIRECT(MONTH($A$1)&":"&M
> > >
>
ONTH($A$1)+11))),$B$1-$A$1,IF(COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11
> >
>
> ))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(COLUMN(INDIRECT(MONTH(
> $A
> > >
>
$1)&":"&MONTH($A$1)+11))=MONTH($B$1)+(YEAR($B$1)-YEAR($A$1))*12,DAY($B$1),DA
> > >
>
Y(DATE(YEAR(A$1),MONTH($A$1)+COLUMN(INDIRECT("1:12")),0))*(MONTH($B$1)+(YEAR
> > >
>
($B$1)-YEAR($A$1))*12>=COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))))))
> > >
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "STFC" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Bob,
> > > >
> > > > This is v impressive. I have some learning to do.
> > > >
> > > > This is close to working, but if the start date = 2nd Feb then Jan
> should
> > > > have 0 days. As start dates and end dates could be any dates in the
> year.
> > > > Also a campaign could just run in 1 month eg 8th Jan - 15th Jan
which
> =8
> > > > days.
> > > >
> > > > Also is it possible to do the array where C1:N1 return the results.
> > > >
> > > > I feel I'm being a bit cheeky now, but thanks for all the help.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > You can actually do it all inj one formula,
> > > > >
> > > > > Select C1:C12, and enter this formula, again as an array formula
> > > > >
> > > > >
> > >
>
=IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
> > > > >
> > >
>
ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
> > > > > OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "Bob Phillips" <[email protected]> wrote in
message
> > > > > news:%[email protected]...
> > > > > > Okay, another shot.
> > > > > >
> > > > > > Assuming that the start date is in A1, end date is in B1, and
the
> > > campaign
> > > > > > days are in C1:C12
> > > > > >
> > > > > > In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
> > > > > > Select all C2:C11 cells together, then enter this array formula
> into
> > > the
> > > > > > formula bar (commit with Ctrlo-Shift-Enter)
> > > > > >
> > > > >
> > >
>
=IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
> > > > > >
> 1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "STFC" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Sorry I haven't been that clear have I.
> > > > > > >
> > > > > > > Yes a more variable formula - the campaigns can run for
anything
> > > from a
> > > > > > week
> > > > > > > to a year.
> > > > > > >
> > > > > > > "Bob Phillips" wrote:
> > > > > > >
> > > > > > > > Are you looking for a more variable formula, or will the
start
> and
> > > end
> > > > > > dates
> > > > > > > > always span 4 months?
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > RP
> > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > >
> > > > > > > >
> > > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > > Hi Bob
> > > > > > > > >
> > > > > > > > > Wow, thanks for the quick response.
> > > > > > > > >
> > > > > > > > > I was really after a formaula that would take into account
> > > partial
> > > > > > months.
> > > > > > > > >
> > > > > > > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> > > > > > > > >
> > > > > > > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days, May=0,
> Jun=0
> > > etc.
> > > > > > This
> > > > > > > > is
> > > > > > > > > for when campaigns ran in a year, and which months it ran
> in.
> > > > > > > > >
> > > > > > > > > Andy.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Bob Phillips" wrote:
> > > > > > > > >
> > > > > > > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > > > > > > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > > > > > > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > > > > > > > > > --
> > > > > > > > > >
> > > > > > > > > > HTH
> > > > > > > > > >
> > > > > > > > > > RP
> > > > > > > > > > (remove nothere from the email address if mailing
direct)
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > > > >
news:[email protected]...
> > > > > > > > > > > Hello,
> > > > > > > > > > >
> > > > > > > > > > > Hope someone can help.
> > > > > > > > > > >
> > > > > > > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th
> April
> > > > > > > > > > >
> > > > > > > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until
> Dec.
> > > > > > > > > > >
> > > > > > > > > > > Therefore for the above example C1 would = 31days D1 =
> > > 28days E1
> > > > > =
> > > > > > > > 31days
> > > > > > > > > > > and F1 = 4 days.
> > > > > > > > > > >
> > > > > > > > > > > I have been playing around with IF and DATEDIF but I'm
> > > getting
> > > > > > > > nowhere.
> > > > > > > > > > Any
> > > > > > > > > > > help would be much appreciated.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>

STFC · 01-26-2005, 12:06 PM

Hi Bob,

the names Andy - I'll change my display name.

This is sooo close now. It works except for campaigns starting and finishing
in the same month. And eg 24th Feb to 10 Mar would return 4 days in Feb and
not 5.

This is making a lot more sense now though - thanks.

Andy

"Bob Phillips" wrote:

> Hi STFC (sorry, don't know your name)
>
> As long as the start date and end date are in the same year, and start month
> will be less than end month, we can simplify the formula and get it the way
> you want
>
> =IF(AND(COLUMN(A1)>MONTH($A$1),COLUMN(A1)<MONTH($B$1)),DAY(DATE(YEAR($A$1),C
> OLUMN(A1)+1,0)),IF(COLUMN(A1)=MONTH($A$1),DAY(DATE(YEAR($A$1),COLUMN(A1)+1,0
> ))-DAY($A$1),IF(COLUMN(A1)=MONTH($B$1),DAY($B$1),0)))
>
> This is NOT an array formula, so just commit with Enter.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "STFC" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob,
> >
> > First time using this site, will definitely use it again. I'm quite glad I
> > was right in thinking this wasn't easy to solve.
> >
> > The formula is v v impressive. The only problem is trying to get a
> campaign
> > that starts in Mar to return 0 for Jan and Feb. Would it be easier to have
> 12
> > separate formulas for C1:N1 to induvidually workout if they had days
> running
> > in that month?
> >
> > Thanks again for all your help, much appreciated.
> >
> > "Bob Phillips" wrote:
> >
> > > I have done it so that C1 contains the month the campaign starts in, so
> Jan
> > > is in C12 (that is next year).
> > >
> > > I spotted the same month problem in my last post, but it can be fixed,
> as
> > > long as the campaign doesn't go over a year). To use C1:N1, use this
> formula
> > >
> > >
> =IF((MONTH($A$1)=MONTH($B$1))*(MONTH($A$1)=COLUMN(INDIRECT(MONTH($A$1)&":"&M
> > >
> ONTH($A$1)+11))),$B$1-$A$1,IF(COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11
> >
> > ))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(COLUMN(INDIRECT(MONTH(
> $A
> > >
> $1)&":"&MONTH($A$1)+11))=MONTH($B$1)+(YEAR($B$1)-YEAR($A$1))*12,DAY($B$1),DA
> > >
> Y(DATE(YEAR(A$1),MONTH($A$1)+COLUMN(INDIRECT("1:12")),0))*(MONTH($B$1)+(YEAR
> > >
> ($B$1)-YEAR($A$1))*12>=COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))))))
> > >
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "STFC" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Bob,
> > > >
> > > > This is v impressive. I have some learning to do.
> > > >
> > > > This is close to working, but if the start date = 2nd Feb then Jan
> should
> > > > have 0 days. As start dates and end dates could be any dates in the
> year.
> > > > Also a campaign could just run in 1 month eg 8th Jan - 15th Jan which
> =8
> > > > days.
> > > >
> > > > Also is it possible to do the array where C1:N1 return the results.
> > > >
> > > > I feel I'm being a bit cheeky now, but thanks for all the help.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > You can actually do it all inj one formula,
> > > > >
> > > > > Select C1:C12, and enter this formula, again as an array formula
> > > > >
> > > > >
> > >
> =IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
> > > > >
> > >
> ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
> > > > > OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "Bob Phillips" <[email protected]> wrote in message
> > > > > news:%[email protected]...
> > > > > > Okay, another shot.
> > > > > >
> > > > > > Assuming that the start date is in A1, end date is in B1, and the
> > > campaign
> > > > > > days are in C1:C12
> > > > > >
> > > > > > In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
> > > > > > Select all C2:C11 cells together, then enter this array formula
> into
> > > the
> > > > > > formula bar (commit with Ctrlo-Shift-Enter)
> > > > > >
> > > > >
> > >
> =IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
> > > > > >
> 1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "STFC" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Sorry I haven't been that clear have I.
> > > > > > >
> > > > > > > Yes a more variable formula - the campaigns can run for anything
> > > from a
> > > > > > week
> > > > > > > to a year.
> > > > > > >
> > > > > > > "Bob Phillips" wrote:
> > > > > > >
> > > > > > > > Are you looking for a more variable formula, or will the start
> and
> > > end
> > > > > > dates
> > > > > > > > always span 4 months?
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > RP
> > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > >
> > > > > > > >
> > > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > > Hi Bob
> > > > > > > > >
> > > > > > > > > Wow, thanks for the quick response.
> > > > > > > > >
> > > > > > > > > I was really after a formaula that would take into account
> > > partial
> > > > > > months.
> > > > > > > > >
> > > > > > > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> > > > > > > > >
> > > > > > > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days, May=0,
> Jun=0
> > > etc.
> > > > > > This
> > > > > > > > is
> > > > > > > > > for when campaigns ran in a year, and which months it ran
> in.
> > > > > > > > >
> > > > > > > > > Andy.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Bob Phillips" wrote:
> > > > > > > > >
> > > > > > > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > > > > > > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > > > > > > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > > > > > > > > > --
> > > > > > > > > >
> > > > > > > > > > HTH
> > > > > > > > > >
> > > > > > > > > > RP
> > > > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > > > > news:[email protected]...
> > > > > > > > > > > Hello,
> > > > > > > > > > >
> > > > > > > > > > > Hope someone can help.
> > > > > > > > > > >
> > > > > > > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th
> April
> > > > > > > > > > >
> > > > > > > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until
> Dec.
> > > > > > > > > > >
> > > > > > > > > > > Therefore for the above example C1 would = 31days D1 =
> > > 28days E1
> > > > > =
> > > > > > > > 31days
> > > > > > > > > > > and F1 = 4 days.
> > > > > > > > > > >
> > > > > > > > > > > I have been playing around with IF and DATEDIF but I'm
> > > getting
> > > > > > > > nowhere.
> > > > > > > > > > Any
> > > > > > > > > > > help would be much appreciated.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Bob Phillips · 01-26-2005, 12:06 PM

Hi Andy (nice to use names ;-)

I've fixed the same month problem in my last post.

For 24th Feb, are you saying that should be 5 days not 4? My test gives 4.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"STFC" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> the names Andy - I'll change my display name.
>
> This is sooo close now. It works except for campaigns starting and
finishing
> in the same month. And eg 24th Feb to 10 Mar would return 4 days in Feb
and
> not 5.
>
> This is making a lot more sense now though - thanks.
>
> Andy
>
> "Bob Phillips" wrote:
>
> > Hi STFC (sorry, don't know your name)
> >
> > As long as the start date and end date are in the same year, and start
month
> > will be less than end month, we can simplify the formula and get it the
way
> > you want
> >
> >
=IF(AND(COLUMN(A1)>MONTH($A$1),COLUMN(A1)<MONTH($B$1)),DAY(DATE(YEAR($A$1),C
> >
OLUMN(A1)+1,0)),IF(COLUMN(A1)=MONTH($A$1),DAY(DATE(YEAR($A$1),COLUMN(A1)+1,0
> > ))-DAY($A$1),IF(COLUMN(A1)=MONTH($B$1),DAY($B$1),0)))
> >
> > This is NOT an array formula, so just commit with Enter.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "STFC" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Bob,
> > >
> > > First time using this site, will definitely use it again. I'm quite
glad I
> > > was right in thinking this wasn't easy to solve.
> > >
> > > The formula is v v impressive. The only problem is trying to get a
> > campaign
> > > that starts in Mar to return 0 for Jan and Feb. Would it be easier to
have
> > 12
> > > separate formulas for C1:N1 to induvidually workout if they had days
> > running
> > > in that month?
> > >
> > > Thanks again for all your help, much appreciated.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > I have done it so that C1 contains the month the campaign starts in,
so
> > Jan
> > > > is in C12 (that is next year).
> > > >
> > > > I spotted the same month problem in my last post, but it can be
fixed,
> > as
> > > > long as the campaign doesn't go over a year). To use C1:N1, use this
> > formula
> > > >
> > > >
> >
=IF((MONTH($A$1)=MONTH($B$1))*(MONTH($A$1)=COLUMN(INDIRECT(MONTH($A$1)&":"&M
> > > >
> >
ONTH($A$1)+11))),$B$1-$A$1,IF(COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11
> > >
> >
> ))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(COLUMN(INDIRECT(MONTH(
> > $A
> > > >
> >
$1)&":"&MONTH($A$1)+11))=MONTH($B$1)+(YEAR($B$1)-YEAR($A$1))*12,DAY($B$1),DA
> > > >
> >
Y(DATE(YEAR(A$1),MONTH($A$1)+COLUMN(INDIRECT("1:12")),0))*(MONTH($B$1)+(YEAR
> > > >
> >
($B$1)-YEAR($A$1))*12>=COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))))))
> > > >
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "STFC" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi Bob,
> > > > >
> > > > > This is v impressive. I have some learning to do.
> > > > >
> > > > > This is close to working, but if the start date = 2nd Feb then Jan
> > should
> > > > > have 0 days. As start dates and end dates could be any dates in
the
> > year.
> > > > > Also a campaign could just run in 1 month eg 8th Jan - 15th Jan
which
> > =8
> > > > > days.
> > > > >
> > > > > Also is it possible to do the array where C1:N1 return the
results.
> > > > >
> > > > > I feel I'm being a bit cheeky now, but thanks for all the help.
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > You can actually do it all inj one formula,
> > > > > >
> > > > > > Select C1:C12, and enter this formula, again as an array formula
> > > > > >
> > > > > >
> > > >
> >
=IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
> > > > > >
> > > >
> >
ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
> > > > > >
OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "Bob Phillips" <[email protected]> wrote in
message
> > > > > > news:%[email protected]...
> > > > > > > Okay, another shot.
> > > > > > >
> > > > > > > Assuming that the start date is in A1, end date is in B1, and
the
> > > > campaign
> > > > > > > days are in C1:C12
> > > > > > >
> > > > > > > In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
> > > > > > > Select all C2:C11 cells together, then enter this array
formula
> > into
> > > > the
> > > > > > > formula bar (commit with Ctrlo-Shift-Enter)
> > > > > > >
> > > > > >
> > > >
> >
=IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
> > > > > > >
> > 1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > RP
> > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > >
> > > > > > >
> > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > Sorry I haven't been that clear have I.
> > > > > > > >
> > > > > > > > Yes a more variable formula - the campaigns can run for
anything
> > > > from a
> > > > > > > week
> > > > > > > > to a year.
> > > > > > > >
> > > > > > > > "Bob Phillips" wrote:
> > > > > > > >
> > > > > > > > > Are you looking for a more variable formula, or will the
start
> > and
> > > > end
> > > > > > > dates
> > > > > > > > > always span 4 months?
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > >
> > > > > > > > > HTH
> > > > > > > > >
> > > > > > > > > RP
> > > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > Hi Bob
> > > > > > > > > >
> > > > > > > > > > Wow, thanks for the quick response.
> > > > > > > > > >
> > > > > > > > > > I was really after a formaula that would take into
account
> > > > partial
> > > > > > > months.
> > > > > > > > > >
> > > > > > > > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> > > > > > > > > >
> > > > > > > > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days,
May=0,
> > Jun=0
> > > > etc.
> > > > > > > This
> > > > > > > > > is
> > > > > > > > > > for when campaigns ran in a year, and which months it
ran
> > in.
> > > > > > > > > >
> > > > > > > > > > Andy.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "Bob Phillips" wrote:
> > > > > > > > > >
> > > > > > > > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > > > > > > > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > > > > > > > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > > > > > > > > > > --
> > > > > > > > > > >
> > > > > > > > > > > HTH
> > > > > > > > > > >
> > > > > > > > > > > RP
> > > > > > > > > > > (remove nothere from the email address if mailing
direct)
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > "STFC" <[email protected]> wrote in
message
> > > > > > > > > > >
news:[email protected]...
> > > > > > > > > > > > Hello,
> > > > > > > > > > > >
> > > > > > > > > > > > Hope someone can help.
> > > > > > > > > > > >
> > > > > > > > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan
B1=4th
> > April
> > > > > > > > > > > >
> > > > > > > > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc
until
> > Dec.
> > > > > > > > > > > >
> > > > > > > > > > > > Therefore for the above example C1 would = 31days D1
=
> > > > 28days E1
> > > > > > =
> > > > > > > > > 31days
> > > > > > > > > > > > and F1 = 4 days.
> > > > > > > > > > > >
> > > > > > > > > > > > I have been playing around with IF and DATEDIF but
I'm
> > > > getting
> > > > > > > > > nowhere.
> > > > > > > > > > > Any
> > > > > > > > > > > > help would be much appreciated.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Myrna Larson · 01-26-2005, 01:06 PM

Here's another approach that will simplify the formulas. In C1:N1 put the
dates of the first of the months, i.e. Jan 1, 2005 through Dec 1, 2005. In
C2:N2, put the last day of each month, i.e. Jan 31, 2005, Feb 28, 2005, ...
Dec 31, 2005. (do that with a formula or literals).

Then, with the start date in A3 and the end date in B3, put this formula in C3

=MAX(MIN($B3,C$2)-MAX($A3,C$1),-1)+1

and copy it across through N3 and down for as many rows as you need.

On Wed, 26 Jan 2005 07:31:06 -0800, "STFC" <[email protected]>
wrote:

>Hi Bob,
>
>the names Andy - I'll change my display name.
>
>This is sooo close now. It works except for campaigns starting and finishing
>in the same month. And eg 24th Feb to 10 Mar would return 4 days in Feb and
>not 5.
>
>This is making a lot more sense now though - thanks.
>
>Andy
>
>"Bob Phillips" wrote:
>
>> Hi STFC (sorry, don't know your name)
>>
>> As long as the start date and end date are in the same year, and start
month
>> will be less than end month, we can simplify the formula and get it the way
>> you want
>>
>>
=IF(AND(COLUMN(A1)>MONTH($A$1),COLUMN(A1)<MONTH($B$1)),DAY(DATE(YEAR($A$1),C
>>
OLUMN(A1)+1,0)),IF(COLUMN(A1)=MONTH($A$1),DAY(DATE(YEAR($A$1),COLUMN(A1)+1,0
>> ))-DAY($A$1),IF(COLUMN(A1)=MONTH($B$1),DAY($B$1),0)))
>>
>> This is NOT an array formula, so just commit with Enter.
>>
>> --
>>
>> HTH
>>
>> RP
>> (remove nothere from the email address if mailing direct)
>>
>>
>> "STFC" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi Bob,
>> >
>> > First time using this site, will definitely use it again. I'm quite glad
I
>> > was right in thinking this wasn't easy to solve.
>> >
>> > The formula is v v impressive. The only problem is trying to get a
>> campaign
>> > that starts in Mar to return 0 for Jan and Feb. Would it be easier to
have
>> 12
>> > separate formulas for C1:N1 to induvidually workout if they had days
>> running
>> > in that month?
>> >
>> > Thanks again for all your help, much appreciated.
>> >
>> > "Bob Phillips" wrote:
>> >
>> > > I have done it so that C1 contains the month the campaign starts in, so
>> Jan
>> > > is in C12 (that is next year).
>> > >
>> > > I spotted the same month problem in my last post, but it can be fixed,
>> as
>> > > long as the campaign doesn't go over a year). To use C1:N1, use this
>> formula
>> > >
>> > >
>>
=IF((MONTH($A$1)=MONTH($B$1))*(MONTH($A$1)=COLUMN(INDIRECT(MONTH($A$1)&":"&M
>> > >
>>
ONTH($A$1)+11))),$B$1-$A$1,IF(COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11
>> >
>> >
))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(COLUMN(INDIRECT(MONTH(
>> $A
>> > >
>>
$1)&":"&MONTH($A$1)+11))=MONTH($B$1)+(YEAR($B$1)-YEAR($A$1))*12,DAY($B$1),DA
>> > >
>>
Y(DATE(YEAR(A$1),MONTH($A$1)+COLUMN(INDIRECT("1:12")),0))*(MONTH($B$1)+(YEAR
>> > >
>> ($B$1)-YEAR($A$1))*12>=COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))))))
>> > >
>> > >
>> > > --
>> > >
>> > > HTH
>> > >
>> > > RP
>> > > (remove nothere from the email address if mailing direct)
>> > >
>> > >
>> > > "STFC" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > > Hi Bob,
>> > > >
>> > > > This is v impressive. I have some learning to do.
>> > > >
>> > > > This is close to working, but if the start date = 2nd Feb then Jan
>> should
>> > > > have 0 days. As start dates and end dates could be any dates in the
>> year.
>> > > > Also a campaign could just run in 1 month eg 8th Jan - 15th Jan
which
>> =8
>> > > > days.
>> > > >
>> > > > Also is it possible to do the array where C1:N1 return the results.
>> > > >
>> > > > I feel I'm being a bit cheeky now, but thanks for all the help.
>> > > >
>> > > > "Bob Phillips" wrote:
>> > > >
>> > > > > You can actually do it all inj one formula,
>> > > > >
>> > > > > Select C1:C12, and enter this formula, again as an array formula
>> > > > >
>> > > > >
>> > >
>>
=IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
>> > > > >
>> > >
>>
ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
>> > > > > OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))
>> > > > >
>> > > > > --
>> > > > >
>> > > > > HTH
>> > > > >
>> > > > > RP
>> > > > > (remove nothere from the email address if mailing direct)
>> > > > >
>> > > > >
>> > > > > "Bob Phillips" <[email protected]> wrote in message
>> > > > > news:%[email protected]...
>> > > > > > Okay, another shot.
>> > > > > >
>> > > > > > Assuming that the start date is in A1, end date is in B1, and the
>> > > campaign
>> > > > > > days are in C1:C12
>> > > > > >
>> > > > > > In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
>> > > > > > Select all C2:C11 cells together, then enter this array formula
>> into
>> > > the
>> > > > > > formula bar (commit with Ctrlo-Shift-Enter)
>> > > > > >
>> > > > >
>> > >
>>
=IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
>> > > > > >
>> 1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
>> > > > > >
>> > > > > > --
>> > > > > >
>> > > > > > HTH
>> > > > > >
>> > > > > > RP
>> > > > > > (remove nothere from the email address if mailing direct)
>> > > > > >
>> > > > > >
>> > > > > > "STFC" <[email protected]> wrote in message
>> > > > > > news:[email protected]...
>> > > > > > > Sorry I haven't been that clear have I.
>> > > > > > >
>> > > > > > > Yes a more variable formula - the campaigns can run for
anything
>> > > from a
>> > > > > > week
>> > > > > > > to a year.
>> > > > > > >
>> > > > > > > "Bob Phillips" wrote:
>> > > > > > >
>> > > > > > > > Are you looking for a more variable formula, or will the
start
>> and
>> > > end
>> > > > > > dates
>> > > > > > > > always span 4 months?
>> > > > > > > >
>> > > > > > > > --
>> > > > > > > >
>> > > > > > > > HTH
>> > > > > > > >
>> > > > > > > > RP
>> > > > > > > > (remove nothere from the email address if mailing direct)
>> > > > > > > >
>> > > > > > > >
>> > > > > > > > "STFC" <[email protected]> wrote in message
>> > > > > > > > news:[email protected]...
>> > > > > > > > > Hi Bob
>> > > > > > > > >
>> > > > > > > > > Wow, thanks for the quick response.
>> > > > > > > > >
>> > > > > > > > > I was really after a formaula that would take into account
>> > > partial
>> > > > > > months.
>> > > > > > > > >
>> > > > > > > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
>> > > > > > > > >
>> > > > > > > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days, May=0,
>> Jun=0
>> > > etc.
>> > > > > > This
>> > > > > > > > is
>> > > > > > > > > for when campaigns ran in a year, and which months it ran
>> in.
>> > > > > > > > >
>> > > > > > > > > Andy.
>> > > > > > > > >
>> > > > > > > > >
>> > > > > > > > >
>> > > > > > > > > "Bob Phillips" wrote:
>> > > > > > > > >
>> > > > > > > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
>> > > > > > > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
>> > > > > > > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
>> > > > > > > > > > --
>> > > > > > > > > >
>> > > > > > > > > > HTH
>> > > > > > > > > >
>> > > > > > > > > > RP
>> > > > > > > > > > (remove nothere from the email address if mailing direct)
>> > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > > > "STFC" <[email protected]> wrote in message
>> > > > > > > > > >
news:[email protected]...
>> > > > > > > > > > > Hello,
>> > > > > > > > > > >
>> > > > > > > > > > > Hope someone can help.
>> > > > > > > > > > >
>> > > > > > > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th
>> April
>> > > > > > > > > > >
>> > > > > > > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until
>> Dec.
>> > > > > > > > > > >
>> > > > > > > > > > > Therefore for the above example C1 would = 31days D1 =
>> > > 28days E1
>> > > > > =
>> > > > > > > > 31days
>> > > > > > > > > > > and F1 = 4 days.
>> > > > > > > > > > >
>> > > > > > > > > > > I have been playing around with IF and DATEDIF but I'm
>> > > getting
>> > > > > > > > nowhere.
>> > > > > > > > > > Any
>> > > > > > > > > > > help would be much appreciated.
>> > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > >
>> > > > > >
>> > > > >
>> > > > >
>> > > > >
>> > >
>> > >
>> > >
>>
>>
>>

Andy W · 01-26-2005, 01:06 PM

Sorry, I didn't see your last post before I posted my reply.

This is even closer now. The campign includes the day it satrts on. Using
your formaula. Start date: 1st Jan End Date: 1st Feb returns 30days for Jan.
For Feb: 24th 25th 26th 27th 28th = 5 days.

I believe there is an end in sight though. Thanks for all your help again.

"Bob Phillips" wrote:

> Hi Andy (nice to use names ;-)
>
> I've fixed the same month problem in my last post.
>
> For 24th Feb, are you saying that should be 5 days not 4? My test gives 4.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "STFC" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob,
> >
> > the names Andy - I'll change my display name.
> >
> > This is sooo close now. It works except for campaigns starting and
> finishing
> > in the same month. And eg 24th Feb to 10 Mar would return 4 days in Feb
> and
> > not 5.
> >
> > This is making a lot more sense now though - thanks.
> >
> > Andy
> >
> > "Bob Phillips" wrote:
> >
> > > Hi STFC (sorry, don't know your name)
> > >
> > > As long as the start date and end date are in the same year, and start
> month
> > > will be less than end month, we can simplify the formula and get it the
> way
> > > you want
> > >
> > >
> =IF(AND(COLUMN(A1)>MONTH($A$1),COLUMN(A1)<MONTH($B$1)),DAY(DATE(YEAR($A$1),C
> > >
> OLUMN(A1)+1,0)),IF(COLUMN(A1)=MONTH($A$1),DAY(DATE(YEAR($A$1),COLUMN(A1)+1,0
> > > ))-DAY($A$1),IF(COLUMN(A1)=MONTH($B$1),DAY($B$1),0)))
> > >
> > > This is NOT an array formula, so just commit with Enter.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "STFC" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Bob,
> > > >
> > > > First time using this site, will definitely use it again. I'm quite
> glad I
> > > > was right in thinking this wasn't easy to solve.
> > > >
> > > > The formula is v v impressive. The only problem is trying to get a
> > > campaign
> > > > that starts in Mar to return 0 for Jan and Feb. Would it be easier to
> have
> > > 12
> > > > separate formulas for C1:N1 to induvidually workout if they had days
> > > running
> > > > in that month?
> > > >
> > > > Thanks again for all your help, much appreciated.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > I have done it so that C1 contains the month the campaign starts in,
> so
> > > Jan
> > > > > is in C12 (that is next year).
> > > > >
> > > > > I spotted the same month problem in my last post, but it can be
> fixed,
> > > as
> > > > > long as the campaign doesn't go over a year). To use C1:N1, use this
> > > formula
> > > > >
> > > > >
> > >
> =IF((MONTH($A$1)=MONTH($B$1))*(MONTH($A$1)=COLUMN(INDIRECT(MONTH($A$1)&":"&M
> > > > >
> > >
> ONTH($A$1)+11))),$B$1-$A$1,IF(COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11
> > > >
> > >
> > ))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(COLUMN(INDIRECT(MONTH(
> > > $A
> > > > >
> > >
> $1)&":"&MONTH($A$1)+11))=MONTH($B$1)+(YEAR($B$1)-YEAR($A$1))*12,DAY($B$1),DA
> > > > >
> > >
> Y(DATE(YEAR(A$1),MONTH($A$1)+COLUMN(INDIRECT("1:12")),0))*(MONTH($B$1)+(YEAR
> > > > >
> > >
> ($B$1)-YEAR($A$1))*12>=COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))))))
> > > > >
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "STFC" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Hi Bob,
> > > > > >
> > > > > > This is v impressive. I have some learning to do.
> > > > > >
> > > > > > This is close to working, but if the start date = 2nd Feb then Jan
> > > should
> > > > > > have 0 days. As start dates and end dates could be any dates in
> the
> > > year.
> > > > > > Also a campaign could just run in 1 month eg 8th Jan - 15th Jan
> which
> > > =8
> > > > > > days.
> > > > > >
> > > > > > Also is it possible to do the array where C1:N1 return the
> results.
> > > > > >
> > > > > > I feel I'm being a bit cheeky now, but thanks for all the help.
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > You can actually do it all inj one formula,
> > > > > > >
> > > > > > > Select C1:C12, and enter this formula, again as an array formula
> > > > > > >
> > > > > > >
> > > > >
> > >
> =IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
> > > > > > >
> > > > >
> > >
> ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
> > > > > > >
> OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > RP
> > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > >
> > > > > > >
> > > > > > > "Bob Phillips" <[email protected]> wrote in
> message
> > > > > > > news:%[email protected]...
> > > > > > > > Okay, another shot.
> > > > > > > >
> > > > > > > > Assuming that the start date is in A1, end date is in B1, and
> the
> > > > > campaign
> > > > > > > > days are in C1:C12
> > > > > > > >
> > > > > > > > In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
> > > > > > > > Select all C2:C11 cells together, then enter this array
> formula
> > > into
> > > > > the
> > > > > > > > formula bar (commit with Ctrlo-Shift-Enter)
> > > > > > > >
> > > > > > >
> > > > >
> > >
> =IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
> > > > > > > >
> > > 1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > RP
> > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > >
> > > > > > > >
> > > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > > Sorry I haven't been that clear have I.
> > > > > > > > >
> > > > > > > > > Yes a more variable formula - the campaigns can run for
> anything
> > > > > from a
> > > > > > > > week
> > > > > > > > > to a year.
> > > > > > > > >
> > > > > > > > > "Bob Phillips" wrote:
> > > > > > > > >
> > > > > > > > > > Are you looking for a more variable formula, or will the
> start
> > > and
> > > > > end
> > > > > > > > dates
> > > > > > > > > > always span 4 months?
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > >
> > > > > > > > > > HTH
> > > > > > > > > >
> > > > > > > > > > RP
> > > > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > > > > news:[email protected]...
> > > > > > > > > > > Hi Bob
> > > > > > > > > > >
> > > > > > > > > > > Wow, thanks for the quick response.
> > > > > > > > > > >
> > > > > > > > > > > I was really after a formaula that would take into
> account
> > > > > partial
> > > > > > > > months.
> > > > > > > > > > >
> > > > > > > > > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> > > > > > > > > > >
> > > > > > > > > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days,
> May=0,
> > > Jun=0
> > > > > etc.
> > > > > > > > This
> > > > > > > > > > is
> > > > > > > > > > > for when campaigns ran in a year, and which months it
> ran
> > > in.
> > > > > > > > > > >
> > > > > > > > > > > Andy.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > "Bob Phillips" wrote:
> > > > > > > > > > >
> > > > > > > > > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > > > > > > > > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > > > > > > > > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > > > > > > > > > > > --
> > > > > > > > > > > >
> > > > > > > > > > > > HTH
> > > > > > > > > > > >
> > > > > > > > > > > > RP
> > > > > > > > > > > > (remove nothere from the email address if mailing
> direct)
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > "STFC" <[email protected]> wrote in
> message
> > > > > > > > > > > >
> news:[email protected]...
> > > > > > > > > > > > > Hello,
> > > > > > > > > > > > >
> > > > > > > > > > > > > Hope someone can help.
> > > > > > > > > > > > >
> > > > > > > > > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan
> B1=4th
> > > April
> > > > > > > > > > > > >
> > > > > > > > > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc
> until
> > > Dec.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Therefore for the above example C1 would = 31days D1
> =
> > > > > 28days E1
> > > > > > > =
> > > > > > > > > > 31days
> > > > > > > > > > > > > and F1 = 4 days.
> > > > > > > > > > > > >
> > > > > > > > > > > > > I have been playing around with IF and DATEDIF but
> I'm
> > > > > getting
> > > > > > > > > > nowhere.
> > > > > > > > > > > > Any
> > > > > > > > > > > > > help would be much appreciated.
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Bob Phillips · 01-26-2005, 01:06 PM

Here we go. Amended to include the start date even if just one month

=IF(AND(MONTH($A$1)=MONTH($B$1),COLUMN(A1)=MONTH($A$1)),$B$1-$A$1+1,IF(AND(C
OLUMN(A1)>MONTH($A$1),COLUMN(A1)<MONTH($B$1)),DAY(DATE(YEAR($A$1),COLUMN(A1)
+1,0)),IF(COLUMN(A1)=MONTH($A$1),DAY(DATE(YEAR($A$1),COLUMN(A1)+1,0))-DAY($A
$1)+1,IF(COLUMN(A1)=MONTH($B$1),DAY($B$1),0))))

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Andy W" <[email protected]> wrote in message
news:[email protected]...
> Sorry, I didn't see your last post before I posted my reply.
>
> This is even closer now. The campign includes the day it satrts on. Using
> your formaula. Start date: 1st Jan End Date: 1st Feb returns 30days for
Jan.
> For Feb: 24th 25th 26th 27th 28th = 5 days.
>
> I believe there is an end in sight though. Thanks for all your help again.
>
> "Bob Phillips" wrote:
>
> > Hi Andy (nice to use names ;-)
> >
> > I've fixed the same month problem in my last post.
> >
> > For 24th Feb, are you saying that should be 5 days not 4? My test gives
4.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "STFC" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Bob,
> > >
> > > the names Andy - I'll change my display name.
> > >
> > > This is sooo close now. It works except for campaigns starting and
> > finishing
> > > in the same month. And eg 24th Feb to 10 Mar would return 4 days in
Feb
> > and
> > > not 5.
> > >
> > > This is making a lot more sense now though - thanks.
> > >
> > > Andy
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Hi STFC (sorry, don't know your name)
> > > >
> > > > As long as the start date and end date are in the same year, and
start
> > month
> > > > will be less than end month, we can simplify the formula and get it
the
> > way
> > > > you want
> > > >
> > > >
> >
=IF(AND(COLUMN(A1)>MONTH($A$1),COLUMN(A1)<MONTH($B$1)),DAY(DATE(YEAR($A$1),C
> > > >
> >
OLUMN(A1)+1,0)),IF(COLUMN(A1)=MONTH($A$1),DAY(DATE(YEAR($A$1),COLUMN(A1)+1,0
> > > > ))-DAY($A$1),IF(COLUMN(A1)=MONTH($B$1),DAY($B$1),0)))
> > > >
> > > > This is NOT an array formula, so just commit with Enter.
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "STFC" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi Bob,
> > > > >
> > > > > First time using this site, will definitely use it again. I'm
quite
> > glad I
> > > > > was right in thinking this wasn't easy to solve.
> > > > >
> > > > > The formula is v v impressive. The only problem is trying to get a
> > > > campaign
> > > > > that starts in Mar to return 0 for Jan and Feb. Would it be easier
to
> > have
> > > > 12
> > > > > separate formulas for C1:N1 to induvidually workout if they had
days
> > > > running
> > > > > in that month?
> > > > >
> > > > > Thanks again for all your help, much appreciated.
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > I have done it so that C1 contains the month the campaign starts
in,
> > so
> > > > Jan
> > > > > > is in C12 (that is next year).
> > > > > >
> > > > > > I spotted the same month problem in my last post, but it can be
> > fixed,
> > > > as
> > > > > > long as the campaign doesn't go over a year). To use C1:N1, use
this
> > > > formula
> > > > > >
> > > > > >
> > > >
> >
=IF((MONTH($A$1)=MONTH($B$1))*(MONTH($A$1)=COLUMN(INDIRECT(MONTH($A$1)&":"&M
> > > > > >
> > > >
> >
ONTH($A$1)+11))),$B$1-$A$1,IF(COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11
> > > > >
> > > >
> >
> ))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(COLUMN(INDIRECT(MONTH(
> > > > $A
> > > > > >
> > > >
> >
$1)&":"&MONTH($A$1)+11))=MONTH($B$1)+(YEAR($B$1)-YEAR($A$1))*12,DAY($B$1),DA
> > > > > >
> > > >
> >
Y(DATE(YEAR(A$1),MONTH($A$1)+COLUMN(INDIRECT("1:12")),0))*(MONTH($B$1)+(YEAR
> > > > > >
> > > >
> >
($B$1)-YEAR($A$1))*12>=COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))))))
> > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "STFC" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Hi Bob,
> > > > > > >
> > > > > > > This is v impressive. I have some learning to do.
> > > > > > >
> > > > > > > This is close to working, but if the start date = 2nd Feb then
Jan
> > > > should
> > > > > > > have 0 days. As start dates and end dates could be any dates
in
> > the
> > > > year.
> > > > > > > Also a campaign could just run in 1 month eg 8th Jan - 15th
Jan
> > which
> > > > =8
> > > > > > > days.
> > > > > > >
> > > > > > > Also is it possible to do the array where C1:N1 return the
> > results.
> > > > > > >
> > > > > > > I feel I'm being a bit cheeky now, but thanks for all the
help.
> > > > > > >
> > > > > > > "Bob Phillips" wrote:
> > > > > > >
> > > > > > > > You can actually do it all inj one formula,
> > > > > > > >
> > > > > > > > Select C1:C12, and enter this formula, again as an array
formula
> > > > > > > >
> > > > > > > >
> > > > > >
> > > >
> >
=IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
> > > > > > > >
> > > > > >
> > > >
> >
ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
> > > > > > > >
> > OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > RP
> > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > >
> > > > > > > >
> > > > > > > > "Bob Phillips" <[email protected]> wrote in
> > message
> > > > > > > > news:%[email protected]...
> > > > > > > > > Okay, another shot.
> > > > > > > > >
> > > > > > > > > Assuming that the start date is in A1, end date is in B1,
and
> > the
> > > > > > campaign
> > > > > > > > > days are in C1:C12
> > > > > > > > >
> > > > > > > > > In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
> > > > > > > > > Select all C2:C11 cells together, then enter this array
> > formula
> > > > into
> > > > > > the
> > > > > > > > > formula bar (commit with Ctrlo-Shift-Enter)
> > > > > > > > >
> > > > > > > >
> > > > > >
> > > >
> >
=IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
> > > > > > > > >
> > > > 1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > >
> > > > > > > > > HTH
> > > > > > > > >
> > > > > > > > > RP
> > > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > Sorry I haven't been that clear have I.
> > > > > > > > > >
> > > > > > > > > > Yes a more variable formula - the campaigns can run for
> > anything
> > > > > > from a
> > > > > > > > > week
> > > > > > > > > > to a year.
> > > > > > > > > >
> > > > > > > > > > "Bob Phillips" wrote:
> > > > > > > > > >
> > > > > > > > > > > Are you looking for a more variable formula, or will
the
> > start
> > > > and
> > > > > > end
> > > > > > > > > dates
> > > > > > > > > > > always span 4 months?
> > > > > > > > > > >
> > > > > > > > > > > --
> > > > > > > > > > >
> > > > > > > > > > > HTH
> > > > > > > > > > >
> > > > > > > > > > > RP
> > > > > > > > > > > (remove nothere from the email address if mailing
direct)
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > "STFC" <[email protected]> wrote in
message
> > > > > > > > > > >
news:[email protected]...
> > > > > > > > > > > > Hi Bob
> > > > > > > > > > > >
> > > > > > > > > > > > Wow, thanks for the quick response.
> > > > > > > > > > > >
> > > > > > > > > > > > I was really after a formaula that would take into
> > account
> > > > > > partial
> > > > > > > > > months.
> > > > > > > > > > > >
> > > > > > > > > > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> > > > > > > > > > > >
> > > > > > > > > > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days,
> > May=0,
> > > > Jun=0
> > > > > > etc.
> > > > > > > > > This
> > > > > > > > > > > is
> > > > > > > > > > > > for when campaigns ran in a year, and which months
it
> > ran
> > > > in.
> > > > > > > > > > > >
> > > > > > > > > > > > Andy.
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > "Bob Phillips" wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > > > > > > > > > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > > > > > > > > > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > > > > > > > > > > > > --
> > > > > > > > > > > > >
> > > > > > > > > > > > > HTH
> > > > > > > > > > > > >
> > > > > > > > > > > > > RP
> > > > > > > > > > > > > (remove nothere from the email address if mailing
> > direct)
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > > "STFC" <[email protected]> wrote in
> > message
> > > > > > > > > > > > >
> > news:[email protected]...
> > > > > > > > > > > > > > Hello,
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Hope someone can help.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan
> > B1=4th
> > > > April
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc
> > until
> > > > Dec.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Therefore for the above example C1 would =
31days D1
> > =
> > > > > > 28days E1
> > > > > > > > =
> > > > > > > > > > > 31days
> > > > > > > > > > > > > > and F1 = 4 days.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > I have been playing around with IF and DATEDIF
but
> > I'm
> > > > > > getting
> > > > > > > > > > > nowhere.
> > > > > > > > > > > > > Any
> > > > > > > > > > > > > > help would be much appreciated.
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Myrna Larson · 01-26-2005, 01:06 PM

Hi, Bob: I think he wants it inclusive on both ends.

On Wed, 26 Jan 2005 16:01:35 -0000, "Bob Phillips"
<[email protected]> wrote:

>Hi Andy (nice to use names ;-)
>
>I've fixed the same month problem in my last post.
>
>For 24th Feb, are you saying that should be 5 days not 4? My test gives 4.

Andy W · 01-26-2005, 01:06 PM

Thats it!!

Thank you so much, now I just need to go through it all so I understand it
myself.

Thanks again.

"Bob Phillips" wrote:

> Here we go. Amended to include the start date even if just one month
>
> =IF(AND(MONTH($A$1)=MONTH($B$1),COLUMN(A1)=MONTH($A$1)),$B$1-$A$1+1,IF(AND(C
> OLUMN(A1)>MONTH($A$1),COLUMN(A1)<MONTH($B$1)),DAY(DATE(YEAR($A$1),COLUMN(A1)
> +1,0)),IF(COLUMN(A1)=MONTH($A$1),DAY(DATE(YEAR($A$1),COLUMN(A1)+1,0))-DAY($A
> $1)+1,IF(COLUMN(A1)=MONTH($B$1),DAY($B$1),0))))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Andy W" <[email protected]> wrote in message
> news:[email protected]...
> > Sorry, I didn't see your last post before I posted my reply.
> >
> > This is even closer now. The campign includes the day it satrts on. Using
> > your formaula. Start date: 1st Jan End Date: 1st Feb returns 30days for
> Jan.
> > For Feb: 24th 25th 26th 27th 28th = 5 days.
> >
> > I believe there is an end in sight though. Thanks for all your help again.
> >
> > "Bob Phillips" wrote:
> >
> > > Hi Andy (nice to use names ;-)
> > >
> > > I've fixed the same month problem in my last post.
> > >
> > > For 24th Feb, are you saying that should be 5 days not 4? My test gives
> 4.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "STFC" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Bob,
> > > >
> > > > the names Andy - I'll change my display name.
> > > >
> > > > This is sooo close now. It works except for campaigns starting and
> > > finishing
> > > > in the same month. And eg 24th Feb to 10 Mar would return 4 days in
> Feb
> > > and
> > > > not 5.
> > > >
> > > > This is making a lot more sense now though - thanks.
> > > >
> > > > Andy
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Hi STFC (sorry, don't know your name)
> > > > >
> > > > > As long as the start date and end date are in the same year, and
> start
> > > month
> > > > > will be less than end month, we can simplify the formula and get it
> the
> > > way
> > > > > you want
> > > > >
> > > > >
> > >
> =IF(AND(COLUMN(A1)>MONTH($A$1),COLUMN(A1)<MONTH($B$1)),DAY(DATE(YEAR($A$1),C
> > > > >
> > >
> OLUMN(A1)+1,0)),IF(COLUMN(A1)=MONTH($A$1),DAY(DATE(YEAR($A$1),COLUMN(A1)+1,0
> > > > > ))-DAY($A$1),IF(COLUMN(A1)=MONTH($B$1),DAY($B$1),0)))
> > > > >
> > > > > This is NOT an array formula, so just commit with Enter.
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "STFC" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Hi Bob,
> > > > > >
> > > > > > First time using this site, will definitely use it again. I'm
> quite
> > > glad I
> > > > > > was right in thinking this wasn't easy to solve.
> > > > > >
> > > > > > The formula is v v impressive. The only problem is trying to get a
> > > > > campaign
> > > > > > that starts in Mar to return 0 for Jan and Feb. Would it be easier
> to
> > > have
> > > > > 12
> > > > > > separate formulas for C1:N1 to induvidually workout if they had
> days
> > > > > running
> > > > > > in that month?
> > > > > >
> > > > > > Thanks again for all your help, much appreciated.
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > I have done it so that C1 contains the month the campaign starts
> in,
> > > so
> > > > > Jan
> > > > > > > is in C12 (that is next year).
> > > > > > >
> > > > > > > I spotted the same month problem in my last post, but it can be
> > > fixed,
> > > > > as
> > > > > > > long as the campaign doesn't go over a year). To use C1:N1, use
> this
> > > > > formula
> > > > > > >
> > > > > > >
> > > > >
> > >
> =IF((MONTH($A$1)=MONTH($B$1))*(MONTH($A$1)=COLUMN(INDIRECT(MONTH($A$1)&":"&M
> > > > > > >
> > > > >
> > >
> ONTH($A$1)+11))),$B$1-$A$1,IF(COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11
> > > > > >
> > > > >
> > >
> > ))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(COLUMN(INDIRECT(MONTH(
> > > > > $A
> > > > > > >
> > > > >
> > >
> $1)&":"&MONTH($A$1)+11))=MONTH($B$1)+(YEAR($B$1)-YEAR($A$1))*12,DAY($B$1),DA
> > > > > > >
> > > > >
> > >
> Y(DATE(YEAR(A$1),MONTH($A$1)+COLUMN(INDIRECT("1:12")),0))*(MONTH($B$1)+(YEAR
> > > > > > >
> > > > >
> > >
> ($B$1)-YEAR($A$1))*12>=COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))))))
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > RP
> > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > >
> > > > > > >
> > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > Hi Bob,
> > > > > > > >
> > > > > > > > This is v impressive. I have some learning to do.
> > > > > > > >
> > > > > > > > This is close to working, but if the start date = 2nd Feb then
> Jan
> > > > > should
> > > > > > > > have 0 days. As start dates and end dates could be any dates
> in
> > > the
> > > > > year.
> > > > > > > > Also a campaign could just run in 1 month eg 8th Jan - 15th
> Jan
> > > which
> > > > > =8
> > > > > > > > days.
> > > > > > > >
> > > > > > > > Also is it possible to do the array where C1:N1 return the
> > > results.
> > > > > > > >
> > > > > > > > I feel I'm being a bit cheeky now, but thanks for all the
> help.
> > > > > > > >
> > > > > > > > "Bob Phillips" wrote:
> > > > > > > >
> > > > > > > > > You can actually do it all inj one formula,
> > > > > > > > >
> > > > > > > > > Select C1:C12, and enter this formula, again as an array
> formula
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > >
> > >
> =IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
> > > > > > > > >
> > > > > > >
> > > > >
> > >
> ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
> > > > > > > > >
> > > OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > >
> > > > > > > > > HTH
> > > > > > > > >
> > > > > > > > > RP
> > > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Bob Phillips" <[email protected]> wrote in
> > > message
> > > > > > > > > news:%[email protected]...
> > > > > > > > > > Okay, another shot.
> > > > > > > > > >
> > > > > > > > > > Assuming that the start date is in A1, end date is in B1,
> and
> > > the
> > > > > > > campaign
> > > > > > > > > > days are in C1:C12
> > > > > > > > > >
> > > > > > > > > > In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
> > > > > > > > > > Select all C2:C11 cells together, then enter this array
> > > formula
> > > > > into
> > > > > > > the
> > > > > > > > > > formula bar (commit with Ctrlo-Shift-Enter)
> > > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > >
> > >
> =IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
> > > > > > > > > >
> > > > > 1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > >
> > > > > > > > > > HTH
> > > > > > > > > >
> > > > > > > > > > RP
> > > > > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > "STFC" <[email protected]> wrote in message
> > > > > > > > > > news:[email protected]...
> > > > > > > > > > > Sorry I haven't been that clear have I.
> > > > > > > > > > >
> > > > > > > > > > > Yes a more variable formula - the campaigns can run for
> > > anything
> > > > > > > from a
> > > > > > > > > > week
> > > > > > > > > > > to a year.
> > > > > > > > > > >
> > > > > > > > > > > "Bob Phillips" wrote:
> > > > > > > > > > >
> > > > > > > > > > > > Are you looking for a more variable formula, or will
> the
> > > start
> > > > > and
> > > > > > > end
> > > > > > > > > > dates
> > > > > > > > > > > > always span 4 months?
> > > > > > > > > > > >
> > > > > > > > > > > > --
> > > > > > > > > > > >
> > > > > > > > > > > > HTH
> > > > > > > > > > > >
> > > > > > > > > > > > RP
> > > > > > > > > > > > (remove nothere from the email address if mailing
> direct)
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > "STFC" <[email protected]> wrote in
> message
> > > > > > > > > > > >
> news:[email protected]...
> > > > > > > > > > > > > Hi Bob
> > > > > > > > > > > > >
> > > > > > > > > > > > > Wow, thanks for the quick response.
> > > > > > > > > > > > >
> > > > > > > > > > > > > I was really after a formaula that would take into
> > > account
> > > > > > > partial
> > > > > > > > > > months.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> > > > > > > > > > > > >
> > > > > > > > > > > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days,
> > > May=0,
> > > > > Jun=0
> > > > > > > etc.
> > > > > > > > > > This
> > > > > > > > > > > > is
> > > > > > > > > > > > > for when campaigns ran in a year, and which months
> it
> > > ran
> > > > > in.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Andy.
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > > "Bob Phillips" wrote:
> > > > > > > > > > > > >
> > > > > > > > > > > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> > > > > > > > > > > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> > > > > > > > > > > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> > > > > > > > > > > > > > --
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > HTH
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > RP
> > > > > > > > > > > > > > (remove nothere from the email address if mailing
> > > direct)
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > "STFC" <[email protected]> wrote in
> > > message
> > > > > > > > > > > > > >
> > > news:[email protected]...
> > > > > > > > > > > > > > > Hello,
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Hope someone can help.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan
> > > B1=4th
> > > > > April
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc
> > > until
> > > > > Dec.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Therefore for the above example C1 would =
> 31days D1
> > > =
> > > > > > > 28days E1
> > > > > > > > > =
> > > > > > > > > > > > 31days
> > > > > > > > > > > > > > > and F1 = 4 days.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > I have been playing around with IF and DATEDIF
> but
> > > I'm
> > > > > > > getting
> > > > > > > > > > > > nowhere.
> > > > > > > > > > > > > > Any
> > > > > > > > > > > > > > > help would be much appreciated.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Andy W · 01-26-2005, 02:06 PM

This solution works as well - I like the simplicity, didn't even think about
approaching it this way. Thanks.

"Myrna Larson" wrote:

> Here's another approach that will simplify the formulas. In C1:N1 put the
> dates of the first of the months, i.e. Jan 1, 2005 through Dec 1, 2005. In
> C2:N2, put the last day of each month, i.e. Jan 31, 2005, Feb 28, 2005, ...
> Dec 31, 2005. (do that with a formula or literals).
>
> Then, with the start date in A3 and the end date in B3, put this formula in C3
>
> =MAX(MIN($B3,C$2)-MAX($A3,C$1),-1)+1
>
> and copy it across through N3 and down for as many rows as you need.
>
>
>
> On Wed, 26 Jan 2005 07:31:06 -0800, "STFC" <[email protected]>
> wrote:
>
> >Hi Bob,
> >
> >the names Andy - I'll change my display name.
> >
> >This is sooo close now. It works except for campaigns starting and finishing
> >in the same month. And eg 24th Feb to 10 Mar would return 4 days in Feb and
> >not 5.
> >
> >This is making a lot more sense now though - thanks.
> >
> >Andy
> >
> >"Bob Phillips" wrote:
> >
> >> Hi STFC (sorry, don't know your name)
> >>
> >> As long as the start date and end date are in the same year, and start
> month
> >> will be less than end month, we can simplify the formula and get it the way
> >> you want
> >>
> >>
> =IF(AND(COLUMN(A1)>MONTH($A$1),COLUMN(A1)<MONTH($B$1)),DAY(DATE(YEAR($A$1),C
> >>
> OLUMN(A1)+1,0)),IF(COLUMN(A1)=MONTH($A$1),DAY(DATE(YEAR($A$1),COLUMN(A1)+1,0
> >> ))-DAY($A$1),IF(COLUMN(A1)=MONTH($B$1),DAY($B$1),0)))
> >>
> >> This is NOT an array formula, so just commit with Enter.
> >>
> >> --
> >>
> >> HTH
> >>
> >> RP
> >> (remove nothere from the email address if mailing direct)
> >>
> >>
> >> "STFC" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hi Bob,
> >> >
> >> > First time using this site, will definitely use it again. I'm quite glad
> I
> >> > was right in thinking this wasn't easy to solve.
> >> >
> >> > The formula is v v impressive. The only problem is trying to get a
> >> campaign
> >> > that starts in Mar to return 0 for Jan and Feb. Would it be easier to
> have
> >> 12
> >> > separate formulas for C1:N1 to induvidually workout if they had days
> >> running
> >> > in that month?
> >> >
> >> > Thanks again for all your help, much appreciated.
> >> >
> >> > "Bob Phillips" wrote:
> >> >
> >> > > I have done it so that C1 contains the month the campaign starts in, so
> >> Jan
> >> > > is in C12 (that is next year).
> >> > >
> >> > > I spotted the same month problem in my last post, but it can be fixed,
> >> as
> >> > > long as the campaign doesn't go over a year). To use C1:N1, use this
> >> formula
> >> > >
> >> > >
> >>
> =IF((MONTH($A$1)=MONTH($B$1))*(MONTH($A$1)=COLUMN(INDIRECT(MONTH($A$1)&":"&M
> >> > >
> >>
> ONTH($A$1)+11))),$B$1-$A$1,IF(COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11
> >> >
> >> >
> ))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(COLUMN(INDIRECT(MONTH(
> >> $A
> >> > >
> >>
> $1)&":"&MONTH($A$1)+11))=MONTH($B$1)+(YEAR($B$1)-YEAR($A$1))*12,DAY($B$1),DA
> >> > >
> >>
> Y(DATE(YEAR(A$1),MONTH($A$1)+COLUMN(INDIRECT("1:12")),0))*(MONTH($B$1)+(YEAR
> >> > >
> >> ($B$1)-YEAR($A$1))*12>=COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))))))
> >> > >
> >> > >
> >> > > --
> >> > >
> >> > > HTH
> >> > >
> >> > > RP
> >> > > (remove nothere from the email address if mailing direct)
> >> > >
> >> > >
> >> > > "STFC" <[email protected]> wrote in message
> >> > > news:[email protected]...
> >> > > > Hi Bob,
> >> > > >
> >> > > > This is v impressive. I have some learning to do.
> >> > > >
> >> > > > This is close to working, but if the start date = 2nd Feb then Jan
> >> should
> >> > > > have 0 days. As start dates and end dates could be any dates in the
> >> year.
> >> > > > Also a campaign could just run in 1 month eg 8th Jan - 15th Jan
> which
> >> =8
> >> > > > days.
> >> > > >
> >> > > > Also is it possible to do the array where C1:N1 return the results.
> >> > > >
> >> > > > I feel I'm being a bit cheeky now, but thanks for all the help.
> >> > > >
> >> > > > "Bob Phillips" wrote:
> >> > > >
> >> > > > > You can actually do it all inj one formula,
> >> > > > >
> >> > > > > Select C1:C12, and enter this formula, again as an array formula
> >> > > > >
> >> > > > >
> >> > >
> >>
> =IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
> >> > > > >
> >> > >
> >>
> ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
> >> > > > > OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))
> >> > > > >
> >> > > > > --
> >> > > > >
> >> > > > > HTH
> >> > > > >
> >> > > > > RP
> >> > > > > (remove nothere from the email address if mailing direct)
> >> > > > >
> >> > > > >
> >> > > > > "Bob Phillips" <[email protected]> wrote in message
> >> > > > > news:%[email protected]...
> >> > > > > > Okay, another shot.
> >> > > > > >
> >> > > > > > Assuming that the start date is in A1, end date is in B1, and the
> >> > > campaign
> >> > > > > > days are in C1:C12
> >> > > > > >
> >> > > > > > In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
> >> > > > > > Select all C2:C11 cells together, then enter this array formula
> >> into
> >> > > the
> >> > > > > > formula bar (commit with Ctrlo-Shift-Enter)
> >> > > > > >
> >> > > > >
> >> > >
> >>
> =IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
> >> > > > > >
> >> 1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
> >> > > > > >
> >> > > > > > --
> >> > > > > >
> >> > > > > > HTH
> >> > > > > >
> >> > > > > > RP
> >> > > > > > (remove nothere from the email address if mailing direct)
> >> > > > > >
> >> > > > > >
> >> > > > > > "STFC" <[email protected]> wrote in message
> >> > > > > > news:[email protected]...
> >> > > > > > > Sorry I haven't been that clear have I.
> >> > > > > > >
> >> > > > > > > Yes a more variable formula - the campaigns can run for
> anything
> >> > > from a
> >> > > > > > week
> >> > > > > > > to a year.
> >> > > > > > >
> >> > > > > > > "Bob Phillips" wrote:
> >> > > > > > >
> >> > > > > > > > Are you looking for a more variable formula, or will the
> start
> >> and
> >> > > end
> >> > > > > > dates
> >> > > > > > > > always span 4 months?
> >> > > > > > > >
> >> > > > > > > > --
> >> > > > > > > >
> >> > > > > > > > HTH
> >> > > > > > > >
> >> > > > > > > > RP
> >> > > > > > > > (remove nothere from the email address if mailing direct)
> >> > > > > > > >
> >> > > > > > > >
> >> > > > > > > > "STFC" <[email protected]> wrote in message
> >> > > > > > > > news:[email protected]...
> >> > > > > > > > > Hi Bob
> >> > > > > > > > >
> >> > > > > > > > > Wow, thanks for the quick response.
> >> > > > > > > > >
> >> > > > > > > > > I was really after a formaula that would take into account
> >> > > partial
> >> > > > > > months.
> >> > > > > > > > >
> >> > > > > > > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
> >> > > > > > > > >
> >> > > > > > > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days, May=0,
> >> Jun=0
> >> > > etc.
> >> > > > > > This
> >> > > > > > > > is
> >> > > > > > > > > for when campaigns ran in a year, and which months it ran
> >> in.
> >> > > > > > > > >
> >> > > > > > > > > Andy.
> >> > > > > > > > >
> >> > > > > > > > >
> >> > > > > > > > >
> >> > > > > > > > > "Bob Phillips" wrote:
> >> > > > > > > > >
> >> > > > > > > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
> >> > > > > > > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
> >> > > > > > > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
> >> > > > > > > > > > --
> >> > > > > > > > > >
> >> > > > > > > > > > HTH
> >> > > > > > > > > >
> >> > > > > > > > > > RP
> >> > > > > > > > > > (remove nothere from the email address if mailing direct)
> >> > > > > > > > > >
> >> > > > > > > > > >
> >> > > > > > > > > > "STFC" <[email protected]> wrote in message
> >> > > > > > > > > >
> news:[email protected]...
> >> > > > > > > > > > > Hello,
> >> > > > > > > > > > >
> >> > > > > > > > > > > Hope someone can help.
> >> > > > > > > > > > >
> >> > > > > > > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan B1=4th
> >> April
> >> > > > > > > > > > >
> >> > > > > > > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc until
> >> Dec.
> >> > > > > > > > > > >
> >> > > > > > > > > > > Therefore for the above example C1 would = 31days D1 =
> >> > > 28days E1
> >> > > > > =
> >> > > > > > > > 31days
> >> > > > > > > > > > > and F1 = 4 days.
> >> > > > > > > > > > >
> >> > > > > > > > > > > I have been playing around with IF and DATEDIF but I'm
> >> > > getting
> >> > > > > > > > nowhere.
> >> > > > > > > > > > Any
> >> > > > > > > > > > > help would be much appreciated.
> >> > > > > > > > > >
> >> > > > > > > > > >
> >> > > > > > > > > >
> >> > > > > > > >
> >> > > > > > > >
> >> > > > > > > >
> >> > > > > >
> >> > > > > >
> >> > > > >
> >> > > > >
> >> > > > >
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>

Myrna Larson · 01-26-2005, 02:06 PM

Yes, I think this kind of formula is much easier to maintain. If you don't
like the "clutter", you can format C1:N1 as "mmm-yy" and hide row 2 (or
vice-versa).

On Wed, 26 Jan 2005 09:05:05 -0800, Andy W <[email protected]>
wrote:

>This solution works as well - I like the simplicity, didn't even think about
>approaching it this way. Thanks.
>
>"Myrna Larson" wrote:
>
>> Here's another approach that will simplify the formulas. In C1:N1 put the
>> dates of the first of the months, i.e. Jan 1, 2005 through Dec 1, 2005. In
>> C2:N2, put the last day of each month, i.e. Jan 31, 2005, Feb 28, 2005, ...
>> Dec 31, 2005. (do that with a formula or literals).
>>
>> Then, with the start date in A3 and the end date in B3, put this formula in
C3
>>
>> =MAX(MIN($B3,C$2)-MAX($A3,C$1),-1)+1
>>
>> and copy it across through N3 and down for as many rows as you need.
>>
>>
>>
>> On Wed, 26 Jan 2005 07:31:06 -0800, "STFC" <[email protected]>
>> wrote:
>>
>> >Hi Bob,
>> >
>> >the names Andy - I'll change my display name.
>> >
>> >This is sooo close now. It works except for campaigns starting and
finishing
>> >in the same month. And eg 24th Feb to 10 Mar would return 4 days in Feb
and
>> >not 5.
>> >
>> >This is making a lot more sense now though - thanks.
>> >
>> >Andy
>> >
>> >"Bob Phillips" wrote:
>> >
>> >> Hi STFC (sorry, don't know your name)
>> >>
>> >> As long as the start date and end date are in the same year, and start
>> month
>> >> will be less than end month, we can simplify the formula and get it the
way
>> >> you want
>> >>
>> >>
>>
=IF(AND(COLUMN(A1)>MONTH($A$1),COLUMN(A1)<MONTH($B$1)),DAY(DATE(YEAR($A$1),C
>> >>
>>
OLUMN(A1)+1,0)),IF(COLUMN(A1)=MONTH($A$1),DAY(DATE(YEAR($A$1),COLUMN(A1)+1,0
>> >> ))-DAY($A$1),IF(COLUMN(A1)=MONTH($B$1),DAY($B$1),0)))
>> >>
>> >> This is NOT an array formula, so just commit with Enter.
>> >>
>> >> --
>> >>
>> >> HTH
>> >>
>> >> RP
>> >> (remove nothere from the email address if mailing direct)
>> >>
>> >>
>> >> "STFC" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Hi Bob,
>> >> >
>> >> > First time using this site, will definitely use it again. I'm quite
glad
>> I
>> >> > was right in thinking this wasn't easy to solve.
>> >> >
>> >> > The formula is v v impressive. The only problem is trying to get a
>> >> campaign
>> >> > that starts in Mar to return 0 for Jan and Feb. Would it be easier to
>> have
>> >> 12
>> >> > separate formulas for C1:N1 to induvidually workout if they had days
>> >> running
>> >> > in that month?
>> >> >
>> >> > Thanks again for all your help, much appreciated.
>> >> >
>> >> > "Bob Phillips" wrote:
>> >> >
>> >> > > I have done it so that C1 contains the month the campaign starts in,
so
>> >> Jan
>> >> > > is in C12 (that is next year).
>> >> > >
>> >> > > I spotted the same month problem in my last post, but it can be
fixed,
>> >> as
>> >> > > long as the campaign doesn't go over a year). To use C1:N1, use this
>> >> formula
>> >> > >
>> >> > >
>> >>
>>
=IF((MONTH($A$1)=MONTH($B$1))*(MONTH($A$1)=COLUMN(INDIRECT(MONTH($A$1)&":"&M
>> >> > >
>> >>
>>
ONTH($A$1)+11))),$B$1-$A$1,IF(COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11
>> >> >
>> >> >
>> ))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(COLUMN(INDIRECT(MONTH(
>> >> $A
>> >> > >
>> >>
>>
$1)&":"&MONTH($A$1)+11))=MONTH($B$1)+(YEAR($B$1)-YEAR($A$1))*12,DAY($B$1),DA
>> >> > >
>> >>
>>
Y(DATE(YEAR(A$1),MONTH($A$1)+COLUMN(INDIRECT("1:12")),0))*(MONTH($B$1)+(YEAR
>> >> > >
>> >>
($B$1)-YEAR($A$1))*12>=COLUMN(INDIRECT(MONTH($A$1)&":"&MONTH($A$1)+11))))))
>> >> > >
>> >> > >
>> >> > > --
>> >> > >
>> >> > > HTH
>> >> > >
>> >> > > RP
>> >> > > (remove nothere from the email address if mailing direct)
>> >> > >
>> >> > >
>> >> > > "STFC" <[email protected]> wrote in message
>> >> > > news:[email protected]...
>> >> > > > Hi Bob,
>> >> > > >
>> >> > > > This is v impressive. I have some learning to do.
>> >> > > >
>> >> > > > This is close to working, but if the start date = 2nd Feb then Jan
>> >> should
>> >> > > > have 0 days. As start dates and end dates could be any dates in
the
>> >> year.
>> >> > > > Also a campaign could just run in 1 month eg 8th Jan - 15th Jan
>> which
>> >> =8
>> >> > > > days.
>> >> > > >
>> >> > > > Also is it possible to do the array where C1:N1 return the
results.
>> >> > > >
>> >> > > > I feel I'm being a bit cheeky now, but thanks for all the help.
>> >> > > >
>> >> > > > "Bob Phillips" wrote:
>> >> > > >
>> >> > > > > You can actually do it all inj one formula,
>> >> > > > >
>> >> > > > > Select C1:C12, and enter this formula, again as an array formula
>> >> > > > >
>> >> > > > >
>> >> > >
>> >>
>>
=IF(ROW(INDIRECT("1:100"))=MONTH($A$1),DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1,IF(
>> >> > > > >
>> >> > >
>> >>
>>
ROW(INDIRECT("1:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$1)+R
>> >> > > > >
OW(INDIRECT("1:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("1:100")))))
>> >> > > > >
>> >> > > > > --
>> >> > > > >
>> >> > > > > HTH
>> >> > > > >
>> >> > > > > RP
>> >> > > > > (remove nothere from the email address if mailing direct)
>> >> > > > >
>> >> > > > >
>> >> > > > > "Bob Phillips" <[email protected]> wrote in
message
>> >> > > > > news:%[email protected]...
>> >> > > > > > Okay, another shot.
>> >> > > > > >
>> >> > > > > > Assuming that the start date is in A1, end date is in B1, and
the
>> >> > > campaign
>> >> > > > > > days are in C1:C12
>> >> > > > > >
>> >> > > > > > In C1: =DATE(YEAR(A$1),MONTH(A$1)+1,1)-A1
>> >> > > > > > Select all C2:C11 cells together, then enter this array
formula
>> >> into
>> >> > > the
>> >> > > > > > formula bar (commit with Ctrlo-Shift-Enter)
>> >> > > > > >
>> >> > > > >
>> >> > >
>> >>
>>
=IF(ROW(INDIRECT("2:100"))=MONTH($B$1),DAY($B$1),DAY(DATE(YEAR(A$1),MONTH(A$
>> >> > > > > >
>> >> 1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)>=ROW(INDIRECT("2:100"))))
>> >> > > > > >
>> >> > > > > > --
>> >> > > > > >
>> >> > > > > > HTH
>> >> > > > > >
>> >> > > > > > RP
>> >> > > > > > (remove nothere from the email address if mailing direct)
>> >> > > > > >
>> >> > > > > >
>> >> > > > > > "STFC" <[email protected]> wrote in message
>> >> > > > > > news:[email protected]...
>> >> > > > > > > Sorry I haven't been that clear have I.
>> >> > > > > > >
>> >> > > > > > > Yes a more variable formula - the campaigns can run for
>> anything
>> >> > > from a
>> >> > > > > > week
>> >> > > > > > > to a year.
>> >> > > > > > >
>> >> > > > > > > "Bob Phillips" wrote:
>> >> > > > > > >
>> >> > > > > > > > Are you looking for a more variable formula, or will the
>> start
>> >> and
>> >> > > end
>> >> > > > > > dates
>> >> > > > > > > > always span 4 months?
>> >> > > > > > > >
>> >> > > > > > > > --
>> >> > > > > > > >
>> >> > > > > > > > HTH
>> >> > > > > > > >
>> >> > > > > > > > RP
>> >> > > > > > > > (remove nothere from the email address if mailing direct)
>> >> > > > > > > >
>> >> > > > > > > >
>> >> > > > > > > > "STFC" <[email protected]> wrote in message
>> >> > > > > > > > news:[email protected]...
>> >> > > > > > > > > Hi Bob
>> >> > > > > > > > >
>> >> > > > > > > > > Wow, thanks for the quick response.
>> >> > > > > > > > >
>> >> > > > > > > > > I was really after a formaula that would take into
account
>> >> > > partial
>> >> > > > > > months.
>> >> > > > > > > > >
>> >> > > > > > > > > Therefore if Start Date = 15 Jan, End Date= 4th Apr
>> >> > > > > > > > >
>> >> > > > > > > > > Jan=17 days, Feb=28days Mar= 31day and Apr=4 days,
May=0,
>> >> Jun=0
>> >> > > etc.
>> >> > > > > > This
>> >> > > > > > > > is
>> >> > > > > > > > > for when campaigns ran in a year, and which months it
ran
>> >> in.
>> >> > > > > > > > >
>> >> > > > > > > > > Andy.
>> >> > > > > > > > >
>> >> > > > > > > > >
>> >> > > > > > > > >
>> >> > > > > > > > > "Bob Phillips" wrote:
>> >> > > > > > > > >
>> >> > > > > > > > > > C1: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
>> >> > > > > > > > > > D1: =DAY(DATE(YEAR(A1),MONTH(A1)+2,0))
>> >> > > > > > > > > > E1: =DAY(DATE(YEAR(A1),MONTH(A1)+3,0))
>> >> > > > > > > > > > --
>> >> > > > > > > > > >
>> >> > > > > > > > > > HTH
>> >> > > > > > > > > >
>> >> > > > > > > > > > RP
>> >> > > > > > > > > > (remove nothere from the email address if mailing
direct)
>> >> > > > > > > > > >
>> >> > > > > > > > > >
>> >> > > > > > > > > > "STFC" <[email protected]> wrote in
message
>> >> > > > > > > > > >
>> news:[email protected]...
>> >> > > > > > > > > > > Hello,
>> >> > > > > > > > > > >
>> >> > > > > > > > > > > Hope someone can help.
>> >> > > > > > > > > > >
>> >> > > > > > > > > > > A1 = Start date B1=End Date eg A1 = 1st Jan
B1=4th
>> >> April
>> >> > > > > > > > > > >
>> >> > > > > > > > > > > C1=Days in Jan D1=Days in Feb E1=Days in Mar etc
until
>> >> Dec.
>> >> > > > > > > > > > >
>> >> > > > > > > > > > > Therefore for the above example C1 would = 31days D1
=
>> >> > > 28days E1
>> >> > > > > =
>> >> > > > > > > > 31days
>> >> > > > > > > > > > > and F1 = 4 days.
>> >> > > > > > > > > > >
>> >> > > > > > > > > > > I have been playing around with IF and DATEDIF but
I'm
>> >> > > getting
>> >> > > > > > > > nowhere.
>> >> > > > > > > > > > Any
>> >> > > > > > > > > > > help would be much appreciated.
>> >> > > > > > > > > >
>> >> > > > > > > > > >
>> >> > > > > > > > > >
>> >> > > > > > > >
>> >> > > > > > > >
>> >> > > > > > > >
>> >> > > > > >
>> >> > > > > >
>> >> > > > >
>> >> > > > >
>> >> > > > >
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >>
>>
>>

Calculating days in a month

LinkBack

Thread Tools

Rate This Thread

Display

Calculating days in a month

Re: Calculating days in a month

RE: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Re: Calculating days in a month

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions