Calculate number of days open from 3 different milestone dates

[jhenderson525]

Issue ID	Issue Discovery Date	QA Approval Date	Issue Closeout	Days Open
1	5/1/2022	5/2/2022	5/4/2022	3
2	5/1/2022	5/5/2022	5/7/2022	6
3	5/5/2022	5/12/2022		7
4	5/13/2022	5/14/2022		1
5	5/10/2022			8
6	5/16/2022			2

(current date is 5/18/2022)

Hi, a newbie here. 

I'm trying to capture the number of days an issue has been open, with the date progressing through three different milestones. Issue Discovery Date, QA Approval Date, and finally Issue Closeout.

My thoughts went to something like:

If Issue Closeout is true, then Closeout minus Discovery = Days Open.

If Closeout is false, then QA Approval – Discovery = Days Open.

If Close and QA Approval is false, then Current Date – Discovery Date = Days Open.

So I have 2 questions (sorry). First, is this the right approach? Second (whether it is or is not the correct approach), any suggested solutions?

[jhenderson525]

@amitchandak Thank you! This solves my problem.

I am realizing i have two Days Open periods i need to record. An active open date period and an inactive period. Ill try to figure that out on my own but this helps greatly. Thanks again!

[amitchandak · Accepted solution]

@jhenderson525 , a new column

datediff([Discovery Date], coalesce([Closeout date], [QA Approval Date], today()) , day)