I am struggling to find the right formula for some data analysis I am currently doing. I have two columns containing dates. Column A contains rows of activation dates. Column B contains rows of de-activation date. I then have a further 12 columns C - N each containing a heading wich is a month and year e.g. Sept_12 , Oct_12 , Nov_12 , Dec 12 and so on.
What I need to know is does each of the months in Column headings C-N fall within the activation and de-activation ranges of columns A & B. I would like to return a 1 for yes and a 0 for no, so I can then sum columns C-N.
I have attached I file which I how gives an example of what I am trying to achieve.
I look forward to hearing from you if you can help.
Provided the data in the activation and deactivation columns are actual formatted dates and the column headings are actual dates being the first of their respective month but formatted to omit the day part, then in cell C2 I suggest
and copied to all other required cells.
If they are not dates then, just working on the text strings, would require a bit of VBA code to crerate a bespoke function ??
Thank you for your help, I understand the logic of your formula but I can't get it to work. I am making sure my cells are formatted as dates, i've tried it in new blank excel sheets but for some reason it isn't working. Any other words of wisdom much appreciated.
Here's Mike's formula in action on your example (see attached).
You might check the following on yours:
1. Column A values are formatted as dates.
2. You won't be able to drag down or across your formulas without locking in some column or row references. Set the formula for the first cell then put $ in front of either the column or row referrence to lock that part of the formula down (see mine). Then you can drag the formula and have it calculate correctly.