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?

Meghan Lundy
edited 12/09/19 in Archived 2016 Posts

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())



Any ideas?


  • Heidi Decker
    Heidi Decker ✭✭✭✭✭✭

    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.


  • Shaine Greenwood

    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."

  • Meghan Lundy
    edited 09/16/16

    Shaine - So the date will always change, each time the sheet is opened?

  • Shaine Greenwood
    Shaine Greenwood Employee
    edited 09/16/16

    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!

  • Meghan Lundy
    edited 09/28/16

    Well darn, that doesn't work the way I want it to.


    Thanks for adding the request.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    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)



This discussion has been closed.