Calculate work order aging with IF statement for blanks.

[Ybenn]

Hello.

I need to calculate the age of a work order using the start date (a2) and end date (b2). The “open” work orders will have a blank end date.

I want to write a formula that will use today’s date as the end date if the end date cell is blank. I’m assuming I use an IF statement, but not sure how to write it.

 

[JoeMo]

=if(b2="",today()-a2,b2-a2)

 

[Ybenn]

=if(b2="",today()-a2,b2-a2)

Thank you!!’

 

[sandy666]

another way

[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

1​

[/td][td]StartDate[/td][td]EndDate[/td][td]Age[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

2​

[/td][td]

10/07/2019​

[/td][td]

20/07/2019​

[/td][td]

10​

[/td][td]=IFERROR(CHOOSE(MATCH(--ISBLANK(B2),{0,1},0),DATEDIF(A2,B2,"d"),DATEDIF(A2,TODAY(),"d")),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

3​

[/td][td]

20/07/2019​

[/td][td]

30/07/2019​

[/td][td]

10​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

4​

[/td][td]

30/07/2019​

[/td][td]

10/08/2019​

[/td][td]

11​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

5​

[/td][td]

10/08/2019​

[/td][td][/td][td]

5​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

6​

[/td][td]

20/08/2019​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

7​

[/td][td]

30/08/2019​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]