Strip Time When Linking Max Modified (Date) Cell to Column with Restrict to Dates

jessie

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!

 

samanthaw

Hi Jessie, 

I think the dateonly function will work well in this case. 

You can read more about the dateonly function HERE. 

And all Smartsheet Functions HERE.

Mike Wilday

Hi Jessie, 

You'll want to nest that Max statement in the dateonly formula like this... 

=DATEONLY(MAX(Modified:Modified)) + ""

 

Paul Newcome

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.

Mike Wilday

Good call, Paul. I figured it was intentional as it was part of their original formula. 

Paul Newcome

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.