How do I add today's date based on another cells data?

Meghan Lundy

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?

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.

Peace!

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

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

Shaine Greenwood

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

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

 

Thanks for adding the request.

J. Craig Williams

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