Count Year within different date formats

[jubilee101]

I have a worksheet with a column which holds project due dates.
Format differs as below, depending on accuracy of finish date of project at current moment.
Apr-18 (Custom Date)
Aug-18 (Custom Date)
2018 - (general format)
Q4 2018 - (general format)

I am using a countifs formula to apply several criteria, with column AF holding the data above. I want to count anything that is in 2018 or -18 or 18
The formula below only counts line items with 2018.

=COUNTIFS('Projects'!A:A,"Yes",'Projects'!B:B,"Yes",'Projects'!C:C,"No",'Projects'!AF:AF,"*18*")

Is this possible?

 

[Special-K99]

If they are held as Excel internal dates (5-digit numbers) the year as 18 or 2018 won't get picked up.
You'd have to convert them using TEXT(A1,"yyyy") something like that

 

[jubilee101]

So what would my new formula be please? or would i need to convert to text in a separate column?

 

[Special-K99]

You probably need SUMPRODUCT cos of the TEXT() on a range

Try
=SUMPRODUCT(--('Projects'!A:A="Yes")*('Projects'!B:B="Yes")*('Projects'!C:C="No")*(TEXT('Projects'!AF:AF,"yyyy"=2018))

 

[jubilee101]

That gives me a #value error. so still not seeing them (thanks for the help by the way)

 

[Special-K99]

Oops!
Should be this

=SUMPRODUCT(--('Projects'!A:A="Yes")*('Projects'!B:B="Yes")*('Projects'!C:C="No")*(TEXT('Projects'!AF:AF,"yyyy")=2018))

Note closing bracket on "yyyy"

You will get a VALUE error if your dates are a mixture of numbers and text

 

[jubilee101]

Ok I tried that and get a zero. Should be over 20.

So what I did was create a new column , set cell format as text and have manually typed everything in. all text, but still looks like
Apr-18
2018
Q4 2018

It's still not counting the 18's
Could I use IF RIGHT formula in some way?

 

[Special-K99]

Can you PM me the file if possible?
If the data is sensitive I would just need the AF column copied into a blank sheet

 

[jubilee101]

can anyone else help? thanks

 

[Jborg]

#6
Try putting the 2018 as "2018"

Text function changes the numeric value to text Format.

Not sure if this the solution.....but try