Forum Discussion
Help with If/And function
Trying to calculate the age of a closed item and getting errors with the below formula. The objective is to calculate how long it took for an item to close (filtering on Closed items only). Thanks
=IF(AND(I68="Closed"),M68<>"",M68-Q68,TODAY()-Q68)
=IF(AND(I68="Closed",M68<>""),M68-Q68,TODAY()-Q68)
Does this work in your sheet? However it's better to share an example without sensitive data which shows the sample data along with the expected result.
=IF(AND(I68="Closed",M68<>""),M68-Q68,TODAY()-Q68)
Does this work in your sheet? However it's better to share an example without sensitive data which shows the sample data along with the expected result.
Thanks for your reply. Per sample table below, I would like to calculate the age of an item as long as it's open, but once status is "closed" would like calculation to stop. Expected result is reflected in last row, where item closed on 10/1 and eventhough today is 10/14, the days calculated is 20.
Status Created date Date last updated Pending 8/9/2024 9/30/2024 66 New 9/1/2024 43 Closed 9/11/2024 10/1/2024 20 Unless I'm totally mistaken, the answer you got from OliverScheurich will work. You just need to make sure the column references are the ones in your example.
Here's that formula, revised using your headings. You just need to substitute the actual columns' letters for what appears [in the square brackets].
=IF(AND([Status]="Closed",[Date Last Updated]=""),[Created Date]-[Date Last Updated],TODAY()-[Date Last Updated])
