Formula to determine whether the month of date falls within a variable season.

**AndrewMac** · 02-05-2014, 10:53 AM

Hi all,

I would be most grateful if someone could help me with this.

I have a date in cell A1, say 31stMay 2014

In cells A2 and A3 I have numbers representing a month of the year, 1 for Jan, 2 for Feb etc which represent the start and end of a season.

I am looking for a formula that will determine whether my date in A1 falls within this season. The season start and end are variables that I want to change. The season could be something like August to March which seems to be making a < or > formula problematic.

Many TIA,

Andrew

**daffodil11** · 02-05-2014, 11:08 AM

I'm sure there's lots of ways. Here's a couple that spring to mind:

=MEDIAN(MONTH(A1),A2:A3)=MONTH(A1)

or

=ISNUMBER(MATCH(MONTH(A1),A2:A3,0))

or

=AND(MONTH(A1)>=A2,MONTH(A1)<=A3)

This assumes the date has to fall between the first of the first month and the end of the second month. Does this work for you or are there deeper specifications?

**AndrewMac** · 02-05-2014, 11:21 AM

Thanks Daff,

Unfortunately these don't quite do what I wanted - perhaps I didn't explain very clearly.

The season might be August to March and I want to determine if a date, say 31st May 2014 falls within this season. The formulas you suggested all come out FALSE for all dates.

However, thanks for your input.

**daffodil11** · 02-05-2014, 11:53 AM

I see what you mean. You may need to indicate a year for your months.

While it would be quite simple to detect if A2<A3, and then have A2 be a month of prior year, that would always assume the months are rolled back and never forward.

Are you able to attach a spreadsheet with a sample of your data, so I can have a better idea of your formatting and layout?

(attach by clicking Go Advanced, Manage Attachments)

Formula to determine whether the month of date falls within a variable season.

LinkBack

Thread Tools

Rate This Thread

Display

Formula to determine whether the month of date falls within a variable season.

Re: Formula to determine whether the month of date falls within a variable season.

Re: Formula to determine whether the month of date falls within a variable season.

Re: Formula to determine whether the month of date falls within a variable season.

Thread Information

Users Browsing this Thread

Similar Threads

COUNTIF the date falls within a month

[SOLVED] Determine date based on request date meanwhile falls on particular days

[SOLVED] Testing whether a date falls within a certain month.

Function to show what season a date falls in.

[SOLVED] Formula to determine whether number falls within range??

Bookmarks

Bookmarks

Posting Permissions