Identify which months fall between a Start and End Date

[CSC]

I’m working on a project that requires that I identify which months a given activity resided in.

The data set I’m working with ranges from 10/1/11 until 3/31/12 in respect to “Start Date”.

Example 1:
Start Date: 10/15/11
End Date: 1/11/12

Answer Needed: October, November, December, January (Populates “Yes” in cells C-F, “no” G and H)

Example 2:
Start Date: 1/12/12
End Date: 1/14/12

Answer Needed: January (Populates “Yes” in cell F “No” in cells C-E, G and H)

Example 3:
Start Date: 11/20/11
End Date: Blank

Answer Needed: November, December, January, February, March (Because the end date the activity isn’t complete. Therefore, the months from start date to March should populate as “Yes” D-H, “No” C)

Attached is an example of what I’m trying to accomplish via a formula. Any help is greatly appreciated as I’m stumped on this one.

Example Start Date End Date Month.xlsx

[dilipandey]

Hi CSC,


Welcome to the forum.

Please find attached the solved workbook for your review. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[CSC]

DILIPandey,
Thanks for the assistance - this is exactly what I was looking for! However, I'm trying to fully understand the formula that you provided me (in hopes I can be self sufficient next time). Can you please explain why you used the following within the formula?

"......<=IF(ISBLANK($B3),DATE(2099,12,31),EOMONTH($B3,0))),"Yes","No")"

The Date (2099,12,31),EOMONTH($B3,0))) portion is throwing me off some.

Also, can you please explain why you added dates (10/1/2011, 11/2/2011) as reference fields as opposed to incorporating the dates into the formula?

Thanks once again for the education.
Chris

[dilipandey]

Hi CSC,

IsBlank($B3) is checking if the end date is missing or not... and if it is missing than you are saying that all should be "yes" and to incorporate that I have included a very far date (31-Dec- 2099)
And if it is present... than I am taking the end date of the same month using EOMONTH function.. which takes the end date of the month for a date.

why I have added dates -> because I need to have dates to reference them in my formula and the date(month) you already have, is a part of some text, hence I could not use that..

Let me know if this is sufficient ... feel free to get back in case of any queries... Happy Learning

Regards,
DILIPandey

<click on below 'star' if this helps>

[CSC]

DILIPandey,
Thanks, for the info - This was very helpful!

Concerning the dates, my question was regarding why are the dates added to the XLS sheet as reference items as opposed to be directly coded within the formula? Can dates be directly incorporated into a formula instead of only being referenced? Sorry for my ignorance, however, I don't work with dates formulas often.

Thanks,
Chris

[dilipandey]

You are welcome Chris..

Yes, Dates can directly be written in the formula itself.. but I would avoid doing that..
if you want you can input date like you have noted in below on your own

"......<=IF(ISBLANK($B3),DATE(2099,12,31),EOMONTH($B3,0))),"Yes","No")"

here 31Dec 2099 is coded in formula itself.

Regards,
DILIPandey

<click on below 'star' if this helps>