Formula to calculate values between two dates with a variable if the cell is blank

[wylie27]

Hello all,

i have a scenario which i am sure is fairly simple to resolve but i have been away from Excel way too long.

I am attempting to calculate the number of days open a ticket was / has been open for a dashboard.

Column A: has opened Date
Column B: Has closed Date.
Colum C: Days Open
Column I: =today()

opened	closed	days open	1/5/20
1/1/20	1/2/20	1
1/1/20		4

However not all tickets have been closed and i would like to workout how many days they have been opened for. Is this possible in the same formula?

it feels like a nested if statement, but i am out of my depth here..

Any help would be appreciated.

 

[JoeMo]

Book1
	A	B	C	D
1	opened	closed	days open	1/5/2020
2	1/1/2020	1/2/2020	1
3	1/1/2020		4
Sheet3

Cell Formulas
Range	Formula
C2:C3	C2	=IF(A2="","",IF(B2="",$D$1-A2,B2-A2))

 

[wylie27]

Thankyou so much! thats quite elegant!

you are a legend!

 

[JoeMo]

Thankyou so much! thats quite elegant!

you are a legend!

You are welcome - thanks for the reply.