CountIf's with a date
Hello-
我想让它的公式te if the task is still open and the date is before today's date then the task is overdue. This is what I have for the cell and it keeps returning unparseable:
=COUNTIFs({NITD Action Log Range 2}, "Open", [{NITD Action Log Range 3}, <=TODAY() ]
Range 2 is a column stating if the task is Open or Closed and Range 3 is a due date column.
Can someone help with this formula?
Thank you!!
Comments
-
Chris McKay ✭✭✭✭✭✭
Hi asuyama,
You're almost there. You just needed to get rid of the square brackets:
=COUNTIFS({NITD Action Log Range 2}, "Open", {NITD Action Log Range 3}, <=TODAY())
I'm assuming that the 2 NITD Action Log ranges are on another sheet?
-
asuyama ✭
Yes it was reference to another sheet. Thank you so much for your help! I do have another one:
I am trying to figure out the average time a task is open, I have a task assigned date column and a due date column and a task open/closed column. What would the formula for that look like?
Thanks!
-
Chris McKay ✭✭✭✭✭✭
Hi asuyama,
You are more than welcome.
To work out the average, I'd suggest making your life easier and provide more granularity by adding 3 hidden columns, then placing the following formulas in the first data row of the appropriate column and finally dragging it down as far as needed:
- Assigned to Open
=IF(AND(LEN([Task Assigned Date]1)>0, LEN([Task Open Date]1)>0), NETDAYS([Task Assigned Date]1, [Task Open Date]1),"") - Open to Close
=IF(AND(LEN([Task Open Date]1)>0, LEN([Task Closed Date]1)>0), NETDAYS([Task Open Date]1, [Task Closed Date]1),"") - Due to Closed
=IF(AND(LEN([Task Closed Date]1)>0, LEN([Task Due Date]1)>0), NETDAYS([Task Closed Date]1, [Task Due Date]1),"")
Once you've done this, you can place the following formulas in the first row of the Task Open Date, Task Closed Date and Task Due Date columns (notthe new hidden columns you created above):
- =AVG([Assigned to Open]:[Assigned to Open])
- =AVG([Open to Closed]:[Open to Closed])
- =AVG([Due to Closed]:[Due to Closed])
- Assigned to Open
Help Article Resources
Categories
Check out theFormula Handbook template!
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/22681/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":36,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"