COUNTIFS to count the records that a date in a columns is before another date

Cesar Perez12

Hello, i am struggling with this formula =COUNTIFS({Status_Arrival}, "SURTIDA", {FECHA DE ENTREGA REAL}, >= {FECHA TENTATIVA}))

I want to count all the records in a sheet that meet the next criteria

1.-Status_Arrival = Surtida

2.- Real arrival date is before or in the same day than the expected arrival day. I try the formula above but the result is zero and theres is actually some records that meet that criteria

KPH

Hi @Cesar Perez12

There is an additional parenthesis at the end of your formula, although that should stop the formula from working, not return the wrong result.

The criteria for the dates is that real is greater than or equal to tentative. In other words real is after or on the same day. You might mean to use < instead of >

Also, the columns containing your dates must be date type. If they are text, smartsheet won’t be able to do any comparisons on the dates.

I hope this helps.

Cesar Perez12

@KPH

Sorry i copied the formula that counts the orders with delay thats why that formula use ">", i really appreciate your help, but that's not the problem i actually got date column types.

KPH

Can you share a screenshot of your data sheet? You can obscure anything that should not be shared publicly. I’d like to see the three columns that you reference in your cross sheet references.

Cesar Perez12

Sure, those are the three columns that i am using

KPH

Hi @Cesar Perez12

I think you need to evaluate whether a row is late or not by adding a new column to your original sheet and then use the value in the new column within your COUNTIF.

So the original sheet would look like this:

The column Helper contains the formula:

=IF(AND(ISDATE([FENCHA ESTIMADA DE ENTREGA]@row), ISDATE([FENCHA DE ENTREGA]@row)), IF([FENCHA ESTIMADA DE ENTREGA]@row < [FENCHA DE ENTREGA]@row, "late", "as expected or early"), "")

This checks that both date columns contain dates. If they do, a formula checks whether the estimated date is before the actual date. If it is, it returns the result "late" and if not, it returns "as expected or early". You can change these words or the criteria.

Now, in your second sheet you can check the value in the Helper column instead of the two date columns.

=COUNTIFS({Status_Arrival}, "SURTIDA", {Helper}, = "late")