I need to calculate past due items with 2 column dates
Hi
I need to create a formula that help me to calculate the past due items but using to columns with dates. One is the the first column with the "Expected closure date". This will be the primary column to check or validate. But we have another one called "Amended closure date", that will have another date that probably will tell us that it is not an item that it is past due. Then we need to check both columns and validate if it is past due. The amended closure date could be in blank. The formula that i have at this moment just to calculate the past due with the expected closure date is this one and it is working fine, but i don't know how to include a new criteria to validate the second column "Amended closure date".
Thanks in advance for anyone help
Actual formula
=IF(COUNTIFS({What is it?}, "Risk", {Severity}, "Sev 1", {Expected Closure Date}, <TODAY()) > 0, COUNTIFS({What is it?}, "Risk", {Severity}, "Sev 1", {Expected Closure Date}, <TODAY()))
Note:
See that the formula was build to leave in blank the cell if it is 0, that is because all the data will help me to create a chart in a Dashboard
Best Answer
-
Are all three columns set to date type columns?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
You will need to add a date column to the source sheet with a formula that will output the correct date to evaluate.
=IF([Amended Closure Date]@row <> "", [Amended Closure Date]@row, [Expected Closure Date]@row)
Then evaluate this one in your metrics sheet.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul, thanks for you answer. I did it but still is not working, as you can see in the screeshoot now is giving me this error #invalid column value
The formula is the one that you gave it to me
=IF([Amended Closure Date]@row <> "", [Amended Closure Date]@row, [Expected Closure Date]@row)
-
Are all three columns set to date type columns?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Yes three columns will be dates types.
I set this formula that works now. The invalid error it was because the column was not set as date property
=IF([Amended Closure Date]@row <> "", [Amended Closure Date]@row, [Expected Closure Date]@row)
As usual thanks Paul
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!