Excel: Help to find # of days between Start Date and Today if Stop Date cell is populated or not

Question

I am stuck and need your help. I am creating a Root Cause spreadsheet in Excel and have identified a few dates that I want to automatically update based on a cell being populated or not.

In cell O27, I have =TODAY() - M2 (where M2 is the 'Start Date' of when the incident occurred and O27 which returns the 'Number of days the incident has been open'). Which is fine so long as the incident is is open. In cell U27, I have the 'End Date', the date the incident was Resolved/ Mitigated. I also have a 'Clock Stop Date' cell if needed in S27.

How do I display the number of days between the 'Start Date' and 'Today' if the 'End Date' cell is not populated and also when the 'End Date' cell is populated?

Answer 1

Use:

=IF(U27<>"",U27,TODAY())-M2