• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate Active Days in a month based on Start & End Dates

Vivek D

Member
I have a list of services with default and revised start and end dates. I want to calculate the the number of days each service is active in any given month. I was able to achieve it using min/max and EOMONTH but it's a very convoluted formula that i'm sure i'll have no clue about if I look at it in a few months.

I'm looking for the simplest way to get the desired output (sample excel attached).

Active Days.png
 

Attachments

  • Active Days.xlsx
    11.2 KB · Views: 7
This is what I came up with. Note that you'll need to keep the line breaks in, as your headers in the First table appear to have them in their name.
=SUMPRODUCT(1*(TEXT(ROW(INDEX($A:$A,IF(Table1[@[Revised Start Date]]="",Table1[@[Default Start Date]],Table1[@[Revised Start Date]])):
INDEX($A:$A,IF(Table1[@[Revised
End Date]]="",Table1[@[Default
End Date]],Table1[@[Revised
End Date]]))),"mmm-yy")=H$3))

Replace H$3 with whatever the cell reference is for your top left header cell.

How it works:
First, we want to define an array that has every date in the date range. We'll use two INDEX functions for that. Each INDEX function figured out whether we are using a revised date or not. Then, knowing that dates = integers in XL, the 2 INDEX functions will give a range like A36534:A37000. We then use ROW function to convert those into numbers, and we have our date array! Huzzah!
Next, we want to check our date array to see if the date fits in the month. We use the TEXT function to convert all the dates into mmm-yy format, and compare to the header. This will produce a giant True/False array. Finally, we convert that array into numbers by multiplying by 1, and the SUMPRODUCT gives us the final total.
 
Back
Top