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.
How do I add today's date based on another cells data?
I know I am missing something simpe, but I want to add "todays" date based on a drop down selection of another cell.
=IF(JAN6 = "Paid", TODAY())
When I use this i get the error: #INVALID COLUMN VALUE and if I add in the column and cell it gives me this:
=IF(JAN6 = "Paid", JAN7 = TODAY())
#CIRCULAR REFERENCE
Any ideas?
Comments
-
Hi Meghan,
I tested your first formula and it works fine. Make sure you have the formula in a column that is a "date" type column.
Peace!
-
Hi Meghan—to add a bit to this, your first formula =IF(JAN6 = "Paid", TODAY()) will work only in a Date type column.
Keep in mind that the formula will continue to update to the current date as long as the conditions remain true, as we don't yet have a way to create a formula based on cell history, such as: add the date that this cell was set to "Paid."
-
Shaine - So the date will always change, each time the sheet is opened?
-
Correct; when the sheet is opened the TODAY() function will change to return values based on the current date, and when the sheet is saved, that new value will remain in the sheet.
For example, if you place the =IF(JAN6 = "Paid", TODAY()) formula in a date column of your sheet today and save, 9/16/16 will be returned. As long as "Paid" exists in your JAN6 cell tomorrow, if you open the sheet tomorrow and save, you'll see 9/17/16.
If JAN6 is changed to no longer contain "Paid" in the cell, the formula will no longer return a value, leaving the cell blank.
I'll get your vote down on our enhancement request list for a way to use cell history in a formula, such as return the date this cell was set to "Paid." We've had a few requests for this type of feature!
-
Well darn, that doesn't work the way I want it to.
Thanks for adding the request.
-
Meghan,
If what you hoping for is to lock the date when the task is paid, then something like this will work IF YOU NEVER MODIFY THE ROW AGAIN.
=IF(JAN6 = "Paid", MIN(Modified6, TODAY()))
where [Modified] is the System Column Modified (Date) column (which must be added)
Because we can't disable formulas (programmatically or manually outside the column), the only other solution is to a date paid column - you can use "d", "t", or "y" to quickly get today's date into the cell (I don't know why "d" and "y" work, I think it is a bug)
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives