Using DATES and MISSING dates
I'm trying to calculate how long a request is open, referencing another sheet.
for example, I want to get the total number of days from start to finish of a request in one sheet, and then average them in a statistic sheet.
I can get the total number using NETWORKDAYS but if there is NO completion date yet, I'm getting this:
#INVALID DATA TYPE which makes complete sense, its trying to calculate a number of days off a date that isn't there
My problem comes up when i'm trying to average. if I dont put anything in the field, and bypass the fields that dont have a date; then the average isn't right because it doesn't include the running number of days the request is still open. I can manually enter in the number of days the request is open, but is there a way to do the following:
when the ACTUAL COMPLETE DATE isn't provided, can the CURRENT DATE be used in a formula?
this is my formula to calculate the number of days from start to finish:
=NETWORKDAY([Requested Date]@row, [Actual Completion Date]@row)
calling REQUESTED DATE and the ACTUAL COMPLETION DATE. if the actual completion date is blank, can SmartSheets input TODAY'S date in that column to populate with a valid date that will then allow for the above calc to work?
Help Article Resources
Check out the Formula Handbook template!