Help creating a COUNTIF formula that will match between dates

[relmasri]

Hi all,

I'm trying to build a formula that will count all the 'X's on the second data sheet for the appropriate TM# between the specific dates (Last Date Worked & Return Date)

The answer to this will go in the collumn titled "VC Days Scheduled". So for example, TM #45225 has X's between 5/30/2016 to 6/3/2016, which falls between his "Last Date Worked" (05/13/2016) and his "Return Date"(6/10/2016)...

So it should return the answer "5" for that specific TM.

Any help would be appreciated! I am guessing a Countif would be in order for this type of scenario.

Thanks in advance

[Jonmo1]

You need to convert the dates in column C to real dates, using same format as column E.

Then use

=SUMPRODUCT(('VC days'!$A$2:$A$6=$A2)*('VC days'!$B$1:$CD$1>=$C2)*('VC days'!$B$1:$CD$1<=$E2)*('VC days'!$B$2:$CD$6="X"))

[relmasri]

Thanks for the response, I'm having trouble converting the C collumn to match the formatting in E. I've tried right clicking and changing the formatting of the cell but it doesn't change. Any reason for that?

[Jonmo1]

Because formatting a cell doesn't actually 'Change' the value contained in the cell, just changes how it is 'Displayed'.

Try highlighting the column, Click Data - Text To Columns - Deliminated - Finish.

[relmasri]

OH okay perfect, I've wondered that before. Good to know. Thanks Jon!

[Jonmo1]

You're welcome.