CountIfs for One Date Greater Than Another Within a Given Period

rfhickey

Hi there!

I'm trying to get a total of projects projected to start between two dates that actually started after their projected start date. This is the formula that I have so far:

=COUNTIFS({Design Document Projected Start Date}, >=DATE(2019, 4, 1), {Design Document Projected Start Date}, <=DATE(2019, 6, 30), {Design Document Actual Start Date}, >{Design Document Projected Start Date})

I am able to get the correct number of projects that started between April 1 and June 30 (the first part of the formula), but when I add the second part of the formula (after 2019, 6, 30), I get 0 and I should be getting 3.

Any thoughts?

Paul Newcome

You will need to add a helper column to the original sheet. The easiest way to go (in my opinion) would be a checkbox with a basic IF statement that will check the box if the actual start is later than the projected start.

 

You would then use THAT column as a range/criteria set.

rfhickey

Thank you, Paul!

I have tried to implement your suggestion, but I'm getting the "INVALID OPERATION" message.

Please see a screenshot of my formula. I'm wondering why I'm getting this.

 

 

Paul Newcome

Hmm... I have been having issues recently with getting that same error when referencing dates. It almost looks like you may have 2 spaces before the >. Try removing one of those (if it's there). If that isn't it, try switching it around to say if the projected is less than the actual.

rfhickey

Thanks for your reply. I tried what you suggested.

It was really odd. I was cell linking those date columns to another sheet. When I removed the cell linking and manually reentered the same dates into the cells of those two date columns, the formula worked.

I have no idea why it works now and didn't before though.

Paul Newcome

Did you try re-linking them?

rfhickey

Thanks, Paul. I played around with relinking them, but I kept getting errors in my formulas when I tried to compare the date columns using the IF function in the symbol column in the linked sheet.

Instead of linking the date columns to the sheet I was aggregating values on, I created a symbol type column in the source sheet and used the same formula, which was able to get working easily on the source sheet. For some reason, linking and them comparing dates using an IF function on a separate sheet that was not the source sheet was throwing the error. Again, it worked fine on the source sheet. I just hid the columns. 

Deep mysteries remain as to the cause of the original error. I got my COUNTIFS() working by testing whether the symbol column was true (and it was true if one date in one column was greater than another date in another column) rather than by testing if the date in one column was greater than the date in another column.

Further, I'm still not understanding why COUNTIFS() doesn't work when comparing two date columns in the arguments and you have to use the intermediary step of using a symbol type column to compare two date columns. I read it doesn't work with numbers in one post by a community mod. 

Paul Newcome

By chance... Are you able to provide a link to the post?

 

And I assume all columns (other than the flag column of course) are formatted as date type columns? This particular instance has me rather curious...

Mike Wilday

This is an interesting issue. Based on our comments in our other post Rfhickey, I would report this to support and come back to both posts and give us an update when you find out more from them. This is clearly a bug. 

rfhickey

Hi, Paul,

Here is the post: https://community.smartsheet.com/discussion/countif-dates

"COUNTIFS can only use < > if you are referring to numbers - not dates."

In the formula I used in the symbol type column, I referenced one date in a date/time-type column and one date in a date-type column. 

rfhickey

Thanks, Mike,

If I have time to reproduce it, I will see if it was a bug or some syntactic error on my end. 

Paul Newcome

I use < and > in a COUNTIFS formula looking at dates A LOT. Most of the time it works. On rare occasion I do run into an issue. See the two below links.

 

https://community.smartsheet.com/discussion/formula-woes

https://community.smartsheet.com/discussion/unexpected-behavior-collect-formula

 

I did reach out to support, and we went back and forth trying a lot of different things. The only consistent workaround we were able to come up with is using a DATEONLY function even though the columns were formatted as date type columns (not system date/time columns).

 

I have WAY too many sheets to go back and change them all, so for me it's just going to have to be a "fix them as they break if they break" kind of thing while using the DATEONLY going forward.

alex44

I just found this DATEONLY approach from this post today after months of having trouble with mixed IF Formulas (where the IF formula included both Date columns and non-Date column). I just tested the DATEONLY solution and it looks good - thanks!