Determine if a month falls between two dates - YEARS OVERLAPPING

**eekbubble** · 05-15-2015, 04:47 AM

Hello, I have been tasked with trying to do the following

Column A - Date
column B -Date

A will always be earlier than B but B might go over to the following year i.e. 1.06.2014 - 14.03.2015

columns C-Z will be labeled months Jan 2014, Feb 2014, March 2014 and so on right up to Dec 2015.

What I need to do is determine if the date between column A and B includes
the given month in column C - Z and return a value of X.

ie...
A B C D E F
Jan 14 Feb 14 Mar 14 Apr 14
1/1/14 3/12/14 X X X blank

I can then count the X values.

Thanks.

**XOR LX** · 05-15-2015, 04:50 AM

Hi.

Perhaps a small example in an actual workbook would help clarify your query.

Regards

**eekbubble** · 05-15-2015, 06:32 AM

Example Doc.xlsx

Hi

Please see attached.

**XOR LX** · 05-15-2015, 06:36 AM

Thanks.

I notice that your "dates" in A2:B3 are not actually Excel-recognised dates, but text entries. Is this the case in reality, or did you just concoct these for the sake of this example?

Regards

**Ali Kırksekiz** · 05-15-2015, 06:45 AM

For C2 cell

Please Login or Register  to view this content.

drag right until Z2 cell then pull down.

**eekbubble** · 05-15-2015, 06:53 AM

XOR LX - It was just the case for the example.

**samba_ravi** · 05-15-2015, 07:27 AM

Try the below formula in C2 and copy across
=IF(AND(EOMONTH(C$1,0)>=SUBSTITUTE($A2,".","/")+0,EOMONTH(C$1,-1)+1<=SUBSTITUTE($B2,".","/")+0),"X","")

**XOR LX** · 05-15-2015, 07:31 AM

@nflsales Not sure I understand. The OP already confirmed that the dates in those cells are actual Excel-recognised dates in reality, so no need for any string substitutions.

As such, Ali Kırksekiz's solution seems perfectly adequate to me.

Regards

**samba_ravi** · 05-15-2015, 07:39 AM

Originally Posted by XOR LX

@nflsales Not sure I understand. The OP already confirmed that the dates in those cells are actual Excel-recognised dates in reality, so no need for any string substitutions.

As such, Ali Kırksekiz's solution seems perfectly adequate to me.

Regards

XOR LX my system not recognized 1.06.14 and 01.08.14 as dates that's why replied with the above

**XOR LX** · 05-15-2015, 07:46 AM

Originally Posted by nflsales

XOR LX my system not recognized 1.06.14 and 01.08.14 as dates that's why replied with the above

Neither does mine. That's why I asked the OP if those dates were actually in that format in their actual workbook. Have you not read the previous posts in this thread?

Regards

**samba_ravi** · 05-15-2015, 07:51 AM

Originally Posted by XOR LX

Neither does mine. That's why I asked the OP if those dates were actually in that format in their actual workbook. Have you not read the previous posts in this thread?

Regards

XOR LX - It was just the case for the example. it was the answer given by "eekbubble" in post number 6 - I was not understand really it's meaning

**eekbubble** · 05-18-2015, 12:03 PM

Hi all

Thanks very much for this, it works very well.

I have an almost identical sheet that I have tried to apply the same formula to but there is a problem in that with this sheet in that not all of the end dates are populated yet (because they are still open). Is there a way of having a cross in the boxes for the month it applies to even if there is no end date yet? The x just goes up to the current month until the end date is populated?

Determine if a month falls between two dates - YEARS OVERLAPPING

LinkBack

Thread Tools

Rate This Thread

Display

Determine if a month falls between two dates - YEARS OVERLAPPING

Re: Determine if a month falls between two dates - YEARS OVERLAPPING

Re: Determine if a month falls between two dates - YEARS OVERLAPPING

Re: Determine if a month falls between two dates - YEARS OVERLAPPING

Re: Determine if a month falls between two dates - YEARS OVERLAPPING

Re: Determine if a month falls between two dates - YEARS OVERLAPPING

Re: Determine if a month falls between two dates - YEARS OVERLAPPING

Re: Determine if a month falls between two dates - YEARS OVERLAPPING

Re: Determine if a month falls between two dates - YEARS OVERLAPPING

Re: Determine if a month falls between two dates - YEARS OVERLAPPING

Re: Determine if a month falls between two dates - YEARS OVERLAPPING

Re: Determine if a month falls between two dates - YEARS OVERLAPPING

Thread Information

Users Browsing this Thread

Similar Threads

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

Lookup total per month (overlapping dates)

[SOLVED] Determine number of months that falls within a given range within two other dates

Chart Problem - Overlapping trend lines and years. Dates do not match.

[SOLVED] Determine years betwen 2 dates

Bookmarks

Bookmarks

Posting Permissions