Matching a DATE in a Formula

Elizabeth Aird

I am trying to streamline this a bit more. I have 14 plants and continual current dates to update. So there has to be an easier way.

I want to find the plant AND match the date from the Date column for the Countifs formula.

=COUNTIFS({StencilDate}, IFERROR(DATE(@CELL),0)=Date3, {Plant}, "Northgate"))

OR

=COUNTIFS({Plant}:{Plant}, "Northgate", {StencilDate}:{StencilDate}, IFERROR(DATE(@CELL), 0)=Date2))

But it's not working. Appreciate your help.

L_123

=COUNTIFS({StencilDate}, Date3, {Plant}, "Northgate")

Give that a try

L_123

=COUNTIFS({StencilDate}, Date3, {Plant}, "Northgate")

Give that a try

Elizabeth Aird

Bless you!!! I was really trying to make it too complicated!

L_123

NP. A couple notes about what I think happened with your formula and how to use the parts you used incorrectly are below:

@cell

@cell is a very useful tool, but generally you only use it if you are using another formula to modify the value, or are checking multiple values on the same range. An example of correct use is below.

=countif(a:a,or(@cell = 1, @cell = 2))

The formula above will count all values of column a where the cell is equal to 1 or 2.

iferror()

The iferror is used in a lot of date formulas as MONTH DAY and YEAR will pop an error if they are used on a blank cell. An example of correct use is below

=countif(date:date,iferror(month(@cell),0) = 1)

The formula above will count all dates in column date where the month is january

date()

Date is used to convert text values into the date format so you can use other date related formula to analyze. If your data is already in date format, and in a date column, you don't need to use this formula. If you do utilize the date formula, you need to put in a year, month, and day individually for it to perform. An example of correct use is below

=date(year(today()),month(today()),1)

The formula above will return the first day of the current month