Calculate avg difference between dates omitting calculation if certain fields are blank

[BrookieOU]

I am trying to calculate the total average difference between dates, but I want to omit including it in the calculation if certain fields are blank. I feel like I should be able to figure this out, but I have tried everything I can think of. I have attached a very basic sample worksheet due to most of the data being confidential. Basically, I have a "Date Sent for Approval" column and a "Date Closed" column. When I send something for approval I enter the date in the "Date Sent for Approval" column. When it is approved and closed I enter the date in the "Date Closed" column. The problem is that I also have instances where both columns are blank, date closed is blank (because its still pending), or the date sent for approval is blank (because we closed it immediately without approval needed). I don't want any of those previous instances to affect my average calculation. I would like to be able to have on my dashboard a box that says "Days to Close" with the average of the difference between the "Date Closed" and "Date Sent for Approval"

Thank you,
Brooke

[AliGW]

Are you still using Excel 2010?

[63falcondude]

The most straightforward way to do this would be to use a helper column and then take the average of that helper column.

For example, you can put this in C2:
=IF(COUNT(A2:B2)=2,B2-A2,"")
Drag down column C

Then this will give you the average:
=AVERAGE(C:C)

I noticed that you used a difference of 1 day in you manual average for 9/22 - 9/22.
This is a difference of 0 days.

If you want those to show as 1, you can modify the helper column to this:
=IF(COUNT(A2:B2)=2,IFERROR(1/(1/(B2-A2)),1),"")

[BrookieOU]

No. Microsoft 365. This was a spreadsheet that was created way before me. I haven't updated because they have some weird formulas and such in here that I don't want to mess up. I have added columns, but that's it. I am going to redo the whole spreadsheet at the start of the year.

[BrookieOU]

You're right about the 1 day in the manual. Allergy medicine has made me foggy.