Strip Time When Linking Max Modified (Date) Cell to Column with Restrict to Dates
Hi Smart Sheet Community!
I am applying a formula to a cell in our project schedule sheets that I found in a couple posts to grab the MAX value of the Modified (Date) column:
=MAX(Modified:Modified) + ""
My colleagues and I will be linking the cell with this formula from our project schedule sheets to a "Last Updated" column project intake list. That "Last Updated" column is restricted to dates only so we can apply conditional formatting that fills the cells in that column a certain color based on age. However, when we link the cells from the "Last Updated" column of our intake list to the cell in our project schedule sheets with the MAX Modified (Date) formula, the cells in our "Last Updated" column populate with the date AND time, even though that column is restricted to dates only, so our conditional formatting does not work.
Is there a way to modify the above formula to strip the time from the Modified (Date) column? If not, is there a way in the linking process to strip out the time and just bring in the date?
Thank you!
Comments
-
Hi Jessie,
You'll want to nest that Max statement in the dateonly formula like this...
=DATEONLY(MAX(Modified:Modified)) + ""
-
NOTE:
The + "" portion is actually converting it to text.Depending on your use, this may not be an issue, but if you are using this data anywhere else, it will have to be converted back to a date to be used as a date whether it be for Automations or other formulas/calculations.
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!
-
Good call, Paul. I figured it was intentional as it was part of their original formula.
-
I did too at first, but then I noticed how it was being placed into a date type column. Converting to text defeats the purpose of a date type column, so I figured I'd throw it out there.
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
- 379 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!