Calculate Days Open?

Hi everyone,

I am struggling with figuring out what formula to use to calculate days open between two date columns on a Sheet.

Column 4 = Submitted At

Column 5 = Closed Date

Column 6 = Days Open

No matter what formula I try to use (NetworkDay/NetworkDays/Sub/Days, etc), I get the #invalid Data Type error. Any ideas why?

Example formula I've tried:

=NETWORKDAY([Submitted At]@row , [Closed Date]@row )

Find more posts tagged with

Accepted answers

HarryS

Hi all!

I figured out what I did wrong and will post my discovery here for future reference in case anyone else runs into the same problem. 😅 When I originally created the sheet the date columns were based on the Text/Number format. I just changed the columns to the Date format, and the formulas are now working as planned!

Thanks for the replies!

All comments

John_Foster

Hi,

Are they all date columns or is one of them the system column that contains the date and time?

We had a similar requirement and created a helper column that hel;d a date only version of the syetem date time field. This column use the formula "=[Created Date]@row" which converts the field to a date field that can then be used in your calculations.

Hope this helps!
John

Andrée Starå

https://community.smartsheet.com/discussion/141355/calculate-days-open

Hi,

I hope you're well and safe!

What column types are the different columns?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

HarryS

Hi all!

Thanks for the replies!

Andrée Starå

https://community.smartsheet.com/discussion/comment/490962#Comment_490962

Glad you got it working!

Happy to help!

✅ Please support the Community by marking your comment as ✅ Answer. This will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.