Getting the difference between a past date and today's date

I want to use the sheet summary to report how long a project has been put on hold. So I have the date that the project got put on hold, which is Aug 9, 2023. I want to automate the calaculation of how many days it has been at any given time.

I've tried multiple formulas and none of them are working. Even typing in =TODAY() returns "#INVALID COLUMN VALUE" when I typed it into a cell under a text/number column type. Please help!

This was my formula and I'm getting nowhere: =TODAY()-DATEONLY(08-09-2023).

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

All comments

Kerry St. Thomas

There are a few issues with your formula, as I see it:
—The function DATEONLY just strips off the timestamp of whatever you supply and returns the date - in your case, Smartsheet saw a text value and then tried to calculate it, hence the error. The correct function to use for that part of your formula is DATE(2023,8,9).
—Date1 - Date2 is icky and I don't like using mathematical operators with dates. While this case is really no big deal, time intelligence does quickly get thorny if you want to exclude weekends or that type of thing, so I try to use all the time functions instead of mathematical operators.

My solution then - that would be put into a column with Text/Number type:
=NETDAYS(DATE(2023,8,9),TODAY())

This counts calendar days without regard to holidays or non-workdays. If you want to count the number of workdays, investigate NETWORKDAY and NETWORKDAYS as the basis of your formula.

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.