Excel Formula Help

[willyg76]

I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month. I just can't get this figured out. HELP!

Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10
10/15/2009 01/13/2009

By the way, I'm using Excel 2007.

Thanks for your help!
Will

 

[phxsportz]

WIth your example, did you want the function to return 89 ?

 

[willyg76]

No. I need it to determine it by month. So it would need to return "17" for October, "30" for November, "31" for December, and "13" for January. (Correction to my original submission, since the date range begins on October 15th, there are actually 17 days in October that are within the date range - Oct 15 thru 31st.)

To provide a little more background, I manage the dollars spent on many different maintenance projects for my company. If a project costs $1 million dollars, instead of the full $1 million being deducted from the budget at the time the project begins, the funds are spread out over the entire length of the project (which is the date range). That date range could be 30 days or 450 days, depending on the project. The dollars "accrue" monthly based on a daily rate. That daily rate is simply determined by dividing the cost of the project by the number of days in the date range. ($1 million dollar project over 75 days, the daily accrual is $1,000,000/74 = $13,513.

My spreadsheet needs to reflect the accrued amount by month (so we can see the monthly effect to the bottom line). Currently, for each project, I have to determine what the daily accrual amount is, and for each month, mutliply that number by the number of days in the month that fall in the date range. This has to be done for thousands of projects. If I could determine a formula do to this automatically, it would save a LOT of time.

If I could at least have a formula to determine the # of days in each month that fall in the date range (separated by month), I can embed that formula and have a formula that references that number to multiply by the daily accrual amount.

Please let me know if you have any more questions. I'm excited at the prospect of making this work!

Thanks again,

Will

 

[phxsportz]

Ok.. Do You want the TOTAL Days in the range ? or do you want a breakdown of Each Month In the Range populated to different cells ?

 

[phxsportz]

This will give you the total days available in a month based on a given date..

=INT(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1) -(DAY(A1)))+1

10/15/08 would return 17 days..

Sorry, but I don't have time to work on this now, but I'll revisit it later and put together a UDF for you to calculate the total days in a range..

 

[T. Valko]

See this:

http://groups.google.com/group/micr...s/browse_thread/thread/b44d79235b8d6e99?hl=en#

 

[willyg76]

I big thank you for everyone's feedback. To submitter "#NAME?", I checked out that link you provided, and one of the formulas worked like a dream. I was even able to insert it into an "if" statement and added some additional references to have it do everything I wanted it to do in one cell!

A couple people at work, that also use such a spreadsheet, were amazed, and want me to build there's the same way. Being a newby to the company, this is AWESOME!

For anyone else that stumbles upon this thread and wants to know what worked for me, this is the post that contained the formula:

If your header row contains dates, i.e. the 1st of each month (you can custom format these as mmm if you just want to display "Jan", "Feb", etc.) then you can use this formula in C2 copied across and down:

=MAX(0,MIN($B2,C$1+31-DAY(C$1+31))-MAX($A2,C$1)+1)

​

Thanks again to everyone for their contribution!

Will

 

[benhlhs]

I LOVE this, but need to take it one step further. I need to do this calc by month, but to only return the networkdays. How does that get built into the formula?? Thanks!

 

[benhlhs]

Oh...and takes into account holidays. Thank you for anyone who can help me get this right!! Thanks!

 

[T. Valko]

I think you'll need to show us some examples and the expected results.