Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Set Up Conditional Formatting to Enter Current Date
I'm fairly new to setting up my own SmartSheet pages and was wondering if this would even be possible. Is there a way to link a Date column to a check box or a drop down item?
For example- I want to have it so that when a certain item is selected from the Dropdown list, a Date column would auto-populate with the current date. Is this possible?
Thanks!
Comments
-
You can do things like these using formulas, but I don't believe you can do what you're looking for.
You can put formulas in date, drop down, symbol, or check box columns. These formulas can do things like checking a box (or not) and change a symbol using a formula.
Within formulas, "TODAY()" will always be "Today". Formulas cannot put 8/9/2017 unless you always want to put 8/9/2017. Tomorrow, "Today" will be 8/10/2017. Does this make sense?
-
I'm not sure we're on the same page here, but it sounds like it'd be easier to do this with a check box rather than a drop down. Let me rephrase:
What would a formula be to populate a "Date" cell once a checkbox is activated in another column? And how would I go about setting this up?
Screenshot attached.
-
Use this formula in the "Date Submitted" Column =IF([Submitted for QC?]1, TODAY())
It seemed to work for me. Once the check box is check it will put todays date in the date column.
-
Obviously this is for row 1, you will have to drag the formula down.
-
To be clear, TODAY() is always today.
A real world example is saying we have three columns:
- Task
- Done (check box)
- Date Finished
If you mark "Done" as checked, with the above formula from Brett, every day you go in to the sheet the date finished will be the date you're checking it on. If you finished it yesterday, the "Date Finished" will be today.
-
Okay...
Let's try this a different way- can I link a checkbox or dropdown item to then change a "date modified" cell next to it? Rather than marking it as TODAY, it'd be date modified.
-
Apparently this requires a plugin to work the way you want it. Check out this thread, anc Craig's response: https://community.smartsheet.com/discussion/formula-date-remaining-static
Smartsheets has no way to do this with a formula, because formulas re-calculate "TODAY" every time you open the sheet, therefore changing the result in the field. It's hard to get until you see it happen.
Anyway, the way around it is to set up a process where you:
- Start with your formula in every row of the column: =IF(Done1, TODAY())
- Click "Done" on a row
- Take the date from the "Completed" column and copy, then Paste Special (Ctrl-Shift-V) in the same cell and select Value. Then you have a manually entered date instead of a formula in that cell.
You might be better off to just type "Aug 16, 2017" in the completed date, but if you want to make errors less likely then it's an option.
Jim
-
Short cut:
Type "t" in a Date column will enter today's date.
Also:
If the LAST THING YOU DO TO A ROW is checking a "done" or "completed" box or some other final, no really, no more updates to the row, you can use the [Modified] column in your formula instead of TODAY(). The system column [Modified] tracks the last time a row was modified (Date/Time).
Craig
-
Craig what would your formula look like in this example? I am looking for the exact same thing when the Status is marked to "Complete" and return the complete date in the Complete Date column.
-
Thank you!!!! This totally worked for me!
