1

I need a formula to look at a date range, i.e., start and end date within 1 calendar year and return yes or no if a named month (in a fixed cell) falls within that range:

ColA              ColB      Fixed cell   Formula would return
01/04/2015     04/06/15       October       No
4
  • please add some example data, it's very difficult to understand your question now. Aug 19, 2015 at 8:11
  • 1
    What do you want to happen if only part of the month is in between the two dates? Aug 19, 2015 at 8:23
  • Can the "named" month by named by month number (e.g., 10 for October)? That would save a translation step.
    – fixer1234
    Aug 19, 2015 at 8:32
  • This is a Q & A site, not a script / formula writing service. Please share what you have researched or attempted so far. Take a look at How to Ask to learn how to improve your question.
    – CharlieRB
    Aug 19, 2015 at 11:37

1 Answer 1

1

The question is a little ambiguous, with several open questions in comments. To cover the bases, I'll provide three examples based on different interpretations of the question.

Example 1

Assumptions:

  • Month inclusion means any part of the month (in your example, April would be considered to fall in the range). See the second example, below, if this assumption is not correct.

  • The fixed cell month is entered as month number (10 instead of October). See the third example, below, if this assumption is not correct.

Lets say the data is in row 2, the fixed cell is C2 and the result is in D2. One formula that would work in D2:

=IF(AND(C2>=MONTH(A2),C2<=MONTH(B2)),"Yes","No")

Example 2 (inclusion = entire month)

If month inclusion means the entire month must be within the date range, you could use a formula like this:

=IF(AND(DATE(YEAR(A2),C2,1)>=A2,DATE(YEAR(A2),C2,DAYSINMONTH(DATE(YEAR(A2),C2,1)))<=B2),"Yes","No")

This compares the first and last day of the month to the date range.

Example 3 (month name)

If the month needs to be entered as month name, you can handle that by replacing all references to C2 in the above formulas with:

MONTH(DATEVALUE(C2&" 1"))

This turns the month name into a date (the first day of that month), and then finds its month number. So, for instance, the formula in Example 1 would be converted to:

=IF(AND(MONTH(DATEVALUE(C2&" 1"))>=MONTH(A2),MONTH(DATEVALUE(C2&" 1"))<=MONTH(B2)),"Yes","No")

One other note: your question suggests that the month in the fixed cell may apply to a collection of date ranges. If so, anchor the references to C2 if you copy the formula to other cells by using $C$2 instead of C2 in the above formulas.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .