Calculate days in month after a given date

Hi all

On the attached example i am trying to determine a few things:
Column C has a launch date of an initiative, and column D shows how many calls per month this initiative is expected to generate.
What I want to achieve when a launch date is entered is as follows:
- produce 0 for any months prior to launch date.
- calculate the number of days left in the month that the launch date is in including actual launch date as day 1 (workdays or weekends does not matter), and calculate the number of calls relative to this (eg if there are 15 days left out of a 28 day month, then 15/28*call volume would be the figure displayed).
- all months following the launch date will have the full amount of calls displayed, regardless of how many days are in the month (ie February shows the same number as March even though they have 28 and 31 days respectively)

I have been able to identify days in the month, as well as taking a date and subtracting the launch date, but cant work out how to limit it to that particular month (eg row 5 takes the first of the month and subtracts the launch date to give a total # of days.

Thanks
Darren.

If I've understood...

Please Login or Register  to view this content.

edit:

a more common approach might be:

Please Login or Register  to view this content.

here however you're calculating the monthly % at all times (in the earlier ex. you calculate only in the month of commencement)

Darren, for the sake of a succinct formula you might look to adapt the 2nd [edit:] approach from my prior post, eg:

Please Login or Register  to view this content.

in the above the following occurs:

i) calculate lesser of (end date +1) and (end of month + 1)

ii) calculate the greater of (start date) and (start of month)

iii) deduct ii) from i) with a MAX trap to ensure a value always >= 0

iv) calculate the number of days within the month (establish day no. of last day in month)

v) determine the apportionment % based on iii) / iv)

vi) calculate amount by multiplying Call Volume by v) %

as outlined, if you have the Analysis ToolPak activated (and/or are using XL2007+) the DATE based functions can be replaced by more succinct ATP alternatives, eg:

Please Login or Register  to view this content.