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?

Find more posts tagged with

Accepted answers

Dale Murphy

@JGEROLD

=IFERROR((NETWORKDAY([Requested Date]@row, [Actual Completion Date]@row)), (NETWORKDAY([Requested Date]@row, TODAY())))

... should be close ... assuming the metric is supposed to include tasks that aren't closed. Beware the use of today() - if a sheet isn't opened or in some other way activated, today() may not update.

Dale

All comments

Dale Murphy

@JGEROLD

=IFERROR((NETWORKDAY([Requested Date]@row, [Actual Completion Date]@row)), (NETWORKDAY([Requested Date]@row, TODAY())))

Dale

JGEROLD

That worked perfectly! thank you so much.

what is the IFERROR function? just for my own reference? 😁

Dale Murphy

I recommend reading about it https://help.smartsheet.com/function/iferror

It can have fairly complex formulae nexted within it, and help prevent messy results especially (as in your case) a bad result causes other things to fail.

in your case, we take advantage of the fact that you know there will be errors and you know the cause AND you can take advantage of them.

Cheers,

Dale

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.