Check if months in dates are within given month

[russellday]

Hi

I have a summary sheet and I want to check that other dates within other tabs are within the given month and return yes if all do match or no if at least one does not.

 

[mohdamir1989]

Hi

I have a summary sheet and I want to check that other dates within other tabs are within the given month and return yes if all do match or no if at least one does not.

Hello Russell,

Please elaborate a bit more or provide sample data.

Although to extract month from date is

=text(cell refrence,"mmm")

this can be combined with if. Still sample data will help

 

[russellday]

So essential I have a summary tab with a cell question that ask are all dates entered within the month. Each other tab has a single date entry. I want the summary tab to check that each of the other tabs single date entry is in the month and return yes if they all do or no.

 

[russellday]

Hi could really do with some help here..

In A2 of Tab1 I need a formula to look at A2 of Tabs 1,2&3 and return "yes" if all the dates contained are within a given month. or "no" if any do not math the month criteria.

 

[russellday]

Please can anyone help with this ????

 

[FormR]

Hi, how many tabs do you have that need checking?

 

[russellday]

Hi around about 5 tabs will need a check

 

[FormR]

Hi, list those 5 sheets in a range on the worksheet and name that reange via the name manager "ShtList" - you can then try somthing like this where the given month is January 2018.

=IF(SUMPRODUCT(COUNTIFS(INDIRECT(ShtList&"!A1"),">=Jan2018",INDIRECT(ShtList&"!A1"),"<=" & EOMONTH("Jan2018",0)))=COUNTA(ShtList),"Yes","No")

 

[russellday]

Thanks for you help but this seems rather complicated and I would welcome a more simple solution.....

 

[FormR]

Thanks for you help but this seems rather complicated

These things often are.

I would welcome a more simple solution.

You could nest the tests in an AND() function somthing like this which is for sheets 1 to 3 which you can continue to extend as needed.

=IF(AND(TEXT(Sheet1!A1,"YYYYMM")="201801",TEXT(Sheet2!A1,"YYYYMM")="201801",TEXT(Sheet3!A1,"YYYYMM")="201801"),"Yes","No")