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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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)&":"&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.
|