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?
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.
-
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?
-
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. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!