Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2012
    Posts
    2

    Unanswered: Is this date within this range?

    Hi,
    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.

    Many Thanks!
    Attached Files Attached Files
    Last edited by scribbler; 10-15-12 at 10:28.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    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
    =IF(AND(C$1>=$A2,C$1<=$B2),1,0)
    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 ??


    MTB

  3. #3
    Join Date
    Oct 2012
    Posts
    2
    Hi MTB,
    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.

    Thanks,

  4. #4
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Scribbler,

    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.

    Josh
    Attached Files Attached Files
    Last edited by scrtchmstj; 11-13-12 at 13:15.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •