Need to calculate # of days per month between two dates even if end date is blank

I have to calculate the numbers of days per month a service has been used based on a start service dated and a discharge (end) date even if the service hasn't ended yet. I have a basic formula that break downs the number of days per each month between two dates, but when the end date is blank the formula just calculates as if end date were end of year. What i need is a way to have the formula calculate that if end date is blank it calculates number of days to current day.

Here is the formula I am currently using: =MAX(0,MIN(EOMONTH(C$1,0),$B2)-MAX(C$1,$A2)+1)

Here is a sample of my data set and the results from above formula:
In Date End Date J F M A M J J A S O N D
6/1/2015 8/30/2015 0 0 0 0 0 30 31 30 0 0 0 0
6/2/2015 0 0 0 0 29 31 31 30 31 30 31 31
6/2/2015 8/28/2015 0 0 0 0 0 29 31 28 0 0 0 0
6/9/2015 0 0 0 0 22 31 31 30 31 30 31 31
6/19/2015 0 0 0 0 12 31 31 30 31 30 31 31

As you can see when no end date is available it calculates max # of days till end of year. I need it to calculate the blank days as if it were the current day. I've tried using ISBLANK but having trouble getting it to work. Please help. Thanks.

I'm sorry, I should have mentioned that I am not proficient at excel.

How do I incorporate your formula into the one I have? I've tried to but get errors. I also tried to put yours in B cells but I get circular error messages.

Thanks again.

Ok. I understand. I just haven't explained my need well. I have a rather large set of data and I need to know the breakdown of days per month for the dates given. I need the break down by month so I can pool the usage data for a particular month and compare it to the number of available units for that month and get a percent use rate. Additionally, some of my dates run from 2014-2015 and I need the breakdown to keep last years days out of the monthly totals (thus the formula I was using). Another problem with my current formula, other that what I stated above, is that if I don't have an end date yet it calculates max days "used" for any remaining months in the year. I would like it to calculate to current date, even if blank so I can have most current data even for current month.

Thanks for being patient and understanding. I appreciate your help.

I hope this helps. In my attachment I have 2 sheets. Sheet1 is the exact data I am trying to use (no sensitive info). As you can see I have the start and end dates and the monthly break down as I'd like to see it. As you move down the column around row 49 you see that some entries do not have end dates. This is because they are still receiving our service. As you can see for these entries without a date the formula I am using appears to just calculate as if the service end date was at end of year and shows all remaining months as fully used up for that entry. What I would like is for the sheet to calculate these empty/still getting service as if today were the end date. Then I would like the sum of each month to transfer to Sheet2 to line 3 for their corresponding month.

On Sheet2 I am trying to get a usage percentage for each month. So, row 3 will have actual units used for that month, and row 2 has available usage (we have 18 units X 31 days = 558 available). I would also like a formula for each month that would calculate the available usage (I.e #days in month X 18 = ???) it would be great if this calculated to the current date as well (like in the September cell, only 2 days this month so far times 18 = 36 available). This would give me a up to date percentage. **Note: I am aware we have more usage than availability at times. We are a drug and alcohol facility and my units are beds, we have extra beds for emergencies that we can not count as available, but we must count that the person was here. Therefor we may get usage rates higher that 100%.**

Usage Rates.xlsx

I hope this help. Thank you again.

Yes!! That works. Thank you.

These are great!!

I only see one issue. With your B2 calculation it is only calculating for previous days of the month. For example the formula when applied to September gives a result of 18, but for our needs it should be 36 because today is the 2nd and this would most accurately reflect our usage for today as well. I'm not sure how to tweek the formula to reflect this.

Perfect!! Your are the best!! Thank you so much.

This can be marked as solved...

Hello. Hi!

The problem is simple, but the answer doesn't seem so...

I'm trying to count the number of days per month per year between two dates.

So for the following 2 dates:

2013.12.15
2014.02.18
I would have something like:

Year | Month | Days

2013 12 16

2014 01 31

2014 02 18

So, with 2 dates, i would get as many rows as months between then.

I would like to achieve this using only a Select statement, but am completelu lost.

Anyone one out there can crack this? 😀

thank you so much for your response. should i start the new thread with the same question