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).
Answers
-
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.
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!