Testing two dates against a month and year

I have a spreadsheet to track projects I’m using the formula below to check whether projects have been active in the current month.

=IF(H6="","",IF(MIN(I6,EOMONTH(TODAY(),0))>=MAX(H6,EOMONTH(TODAY(),-1)+1),"Yes","No"))

I now though need to alter the formula to test whether projects have been active in a specified month. I want to do this using a formula rather than VBA.

Two columns in the spreadsheet give the start and end date of the projects. Two cells using drop-down lists allow the user to select a month and year for which they want to check how many projects have been active in that specified month/year – the results of the formula are used in a summary table that displays the number of projects active in the selected month by using COUNTIFS to count the number of ‘Yes’ results.

The attached file contains sample data which with column F showing the results I would expect to be returned if the month "January" (D2) and year "2018" (E2) are selected, and the image below shows the layout of this.

example.gif

The formula needs to:

• Condition 1: test If the month and year of the date in C6 is before or equal to the month and year selected by D2 and E2 (e.g. if 12/12/2017 occurs before or during January 2018)
• Condition 2: test If the month and year of the date in D6 is equal to or after the month and year selected by D2 and E2 (e.g. if 18/03/2018 occurs after or during January 2018)
• return "Yes" if both these conditions are TRUE
• return "Yes" if Condition 1 is TRUE but cell D6 is blank
• return "No" if neither condition is TRUE
• return a blank value if both cells are blank

I think the formula I’m currently using to test if a project’s active in the current month can probably be tweaked to do this by replacing the EOMONTH(TODAY()…), but can’t figure out how to get the month and year into the formula with having only a month and year rather than a date with a day, month, and year. I did wonder if I perhaps needed to create a couple of helper cells that could give the dates 01/mm/yyyy and the last day of the month/year by concatenating the month and year with "1" and the last day of the month, but obviously that would change depending on how many days there are in the selected month. I then realised that if the year was a leap year, this would change the date of the last day of the month of February and realised it would be more complicated than I’d first envisaged.

I think that I need three formulas to accomplish this – one to get the first day of the month/year, a second to get the last day of the month, and a third, using these two dates, to test against the project start and end date, but I’m not sure which of the date functions I need.

Any guidance would be appreciated.

For G2

Formula:

Please Login or Register  to view this content.

For H2

Formula:

Please Login or Register  to view this content.

then in F6

Formula:

Please Login or Register  to view this content.

Kind regards
Leo

Sorry it's taken me a while to reply - a bit of a family emergency has tied me up for the last couple of days.

Thank you so much for your help guys. I've finally got my spreadsheet working correctly now, which has taken me the best part of a month just working on it evenings - I can reclaim my evenings now!

Ben, I hadn't spotted the error in my criteria - of course you're right. I've got your formula working great now. Initially when I tried it, it was returning #VALUE and I couldn't figure out why until I noticed that you're in the US. Being in the UK my date format is dd/mm/yyyy and of course when I looked closely at your formula it's in the mm/dd/yyyy format. As soon as I switched it round it works great! Thank you so much for your help.

Leo, thanks for your suggestion. There's a problem with your formula though. It returns a result, but when I did some testing of different month/year combinations, if the month/year selected is after month/year selected it doesn't always return the correct value. I've attached an amended file with a couple of extra columns added to the sample data that I used to test the two solutions - it shows the results if the month 'January' and year '2018' are selected (I've just put some conditional formatting on the two columns to highlight whether the results matched the expected results). For example if January 2018 is selected, if the start date is later than that, e.g. 23/02/2018 where you would expect the result to be 'No', the returned value is 'Yes' (though I've been able to use the part of your solution for getting the month and year into a date format for another spreadsheet I'm working on, so two spreadsheets solved in one go! )

Thanks again.