Calculating Days
Answers
-
Do you want workdays or calendar days?
Work Days =networkdays([Date Column 1]24, [Date Column 2]24)
Calendar Days =netdays([Date Column 1]24, [Date Column 2]24)
replace the Date Column 1 and Date Column 2 with the actual column name. 24 assumes this is row 24.
https://help.smartsheet.com/function/netdays
https://help.smartsheet.com/function/networkdays
Enjoy.
-
I'm trying to do this and I keep getting the result of #INVALID DATA TYPE. I've checked my helper column properties, and both are "date". They have been defaulted to MM/DD/YY by smartsheet. Do I need a different date layout for the formula to work?
On a side note, when I originally import the data, my dates include timestamps, but because I know smartsheet doesn't play well with time, I've taken to adding helper columns that change it to just the date format... could the helper columns be the issue here?
-
@JLK It depends on what formula(s) you have in your helper columns. What formula(s) are you using exactly?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
Thanks for helping me figure this out. My formula for the helper column is:
=[name of column]@row
which should return exactly whats in my original date + timestamp column (formatted as: YYYY-MM-DD 00:00:00), but then I apply the column property of date and it returns just the MM/DD/YY
If there is a better way to do this I'm all ears.
-
Are you able to provide some screenshots for context?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Here is the column as imported:
And here is the helper column formula: =[date applied (UCT)]@row
And here is what the helper returns:
It used to not return the time, but when I logged back in to get you these screenshots, the time appears to still be there, so probably the first step is getting rid of the times?
-
So even though you are using a date column, you are still only populating a text string. To get just the date pulled out of the string and stored as a date value, you would use something like this:
=DATE(VALUE(LEFT([date applied (UTC)]@row, 4)), VALUE(MID([date applied (UTC)]@row, 6, 2)), VALUE(MID([date applied (UTC)]@row, 8, 2)))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks Paul,
What I'm getting when I use this formula is #Invalid Value... thoughts?
-
Sorry about that. I miscounted the number of characters. Try this instead:
=DATE(VALUE(LEFT([date applied (UTC)]@row, 4)), VALUE(MID([date applied (UTC)]@row, 6, 2)), VALUE(MID([date applied (UTC)]@row, 9, 2)))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!