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)&":"&MO NTH($A$1)+11))))))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"STFC" wrote in message
...
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(D ATE(YEAR(A$1),MONTH(A$1)+R
OW(INDIRECT("1:100")),0))*(MONTH($B$1)=ROW(INDIRE CT("1:100")))))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Bob Phillips" wrote in message
...
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),D AY(DATE(YEAR(A$1),MONTH(A$
1)+ROW(INDIRECT("2:100")),0))*(MONTH($B$1)=ROW(IN DIRECT("2:100"))))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"STFC" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.
|