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!

Find more posts tagged with

Cell linking

Media & Entertainment

Comments

MattH

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?

aventura

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.

Screen Shot 2017-08-09 at 11.08.57 AM.png

Brett Wallace

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.

Brett Wallace

Obviously this is for row 1, you will have to drag the formula down.

MattH

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.

aventura

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.

CanadaJim

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

J. Craig Williams

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

Scott Arnold

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.

Screenshot Of Smartsheet.PNG