Date problem

cpo1wh

I recently ran into a problem with dates that were hard "mm/dd/yy" coded into a formula and populated in a date field. For read only purposes, this is fine, but it becomes a problem if you try to use the hard coded date for comparison with another date field, such as TODAY(). I did not realize that TODAY()'s date is different than "mm/dd/yy".

My question now is: Is there a way to populate a cell with today's date (statically) without using TODAY() or "mm/dd/yy"? Using TODAY() is not an option because it will change tomorrow and I want a static date (albeit today) populated into the cell. I also want to be able to use the date field with formulas if necessary and that eliminates using "mm/dd/yy". I did not realize that a date column comes in different flavors; hence the problem. I suppose it would be nice if smartsheet converted text dates "mm/dd/yy" automatically (as an option) to be compliant with calendar and TODAY() dates.

The code below (thankfully provided by Paul Newcome) works fine for turning on an RGB ball as long as you hand key the date into the [Status Date] date cell or use the pop-up calendar and do not automatically populate (in this case [Status Date] with a hard coded "mm/dd/yy". If "mm/dd/yy" is used to automatically populate the [Status Date] cell from another cell formula, an error message occurs when trying to use the code below. With the below code, the RBG balls only populate when the conditions are met. I've attached a snippet of the database below.

=IF(AND(ISDATE([Status Date]@row), ISDATE(Today@row)), IF([Status Date]@row >= Today@row - 14, "Green", "Red", "")))

My goal is to populate a date cell with today's date using a conditional formula and have the date remain constant even with a change to tomorrow. To my knowledge, there is no way to use TODAY() and have it lock down as a static date. An example use for this would be "if xyz cell is not blank, populate a certain row date cell as the xyz becomes populated."

It would also be nice if there was a way to search and replace code in formulas on a global basis. Maybe there is and I just don't know about it yet. Search and replace has always been one of my favorite functions when using digital media. It would also be nice if you could turn on all of the formulas in a Smartsheet file to see where all the formulas are at one time. Maybe that's possible too, but if not, it would be a nice troubleshooting feature in my opinion.

I realize I'm in over my head, but learning is all about getting light bulbs to come on, even if a few at a time. Hopefully I'll be able to turn a few on and become a little more proficient. I appreciate this Smartsheet community and its contributors.Thank you for any suggestions.

 

Sean Morgan

Hello. This post has been identified as a duplicate on a similar account. I have set the original post as published, allowing you to delete this post.

Many Thanks

Sean

Community Moderator