Formula Returning Today's Date IF Column is Value

Possibly a dumb question...

I am trying to format a date cell to return todays date if a cell in the same row is marked "Completed" using the IF formula.

I am using the TODAY() formula as a result in the IF string, however it looks like the today formula cannot not work in this way:

=IF(Status111 = "Completed", "TODAY()", "")

The formula just returns the text "TOADY()" , which I imagine is because the formula only accepts text in the TRUE or FALSE conditions.

Is there another method to achieving this function?

Find more posts tagged with

Formulas

Conditional Formatting

Comments

Stephen Uekert

Hello Devan,

From the looks of your formula, you had it about 99% right. I think if you just remove the "" around TODAY you may get the result you want. I think the formula should read:

=IF(Status111 = "Completed", TODAY())

I tested it and it seemed to work, but give it a try and see.

Take care and have a great day!

Steve

Devan Morin

Hi Steve,

I figured I must have been inputing the formula incorrectly.

Thanks a million for the help!

- Devan

Stephen Uekert

No problem. Glad I could help!

Chris Goodman

Hi Devan,

I'm guessing I'm attempting to do something similar.

I have a to-do list and when the item is marked completed, I want a field 'Date Completed' to be auto-populated with the date it was compelted.

My question is if I completed a task 11.29 will the formula always update to today's (the current) date (11/30) or will it keep the date when it was completed.

You can save me 24 hours of intense waiting

Thanks,

Chris

Travis

Chris, the TODAY() function will show *todays* date and will change when the day changes. If marking an item as complete is the last change you make in a row, you can use the Modified (Date) column which shows a timestamp of when the row was last modified. This column can be found under the Auto-Number/System section when adding a new column.

Travis

Chris sent me an email asking if there was another way to show a timestamp of when a cell was last edited (the cell might not be the last change in the row, so my suggestion wouldnt work).

While there is not a built in method for this, it is possible with a third party solution - Azuqua. If anyone would like information on how this can be done with Azuqua, let me know and I can get my contact from Azuqua to post here steps to take. Keep in mind this is a paid service but they offer a free trial.

Devan Morin

Hi Chris,

I did realize after implementing Steve’s original suggestion that my "Completed" date column would always change to reflect the current date, not the date it was marked "complete" as I had originally intended.

I am now just manually selecting the "Today" button on each date cell once I have completed that item. I would still be interested in a way to automate this so that I can have the cell stamp the current date using a formula.

I am going to see about posting this in the feature request section.

Cheers!

Travis

One more suggestion for this, if you dont need the date displayed in a cell. All cells have cell history including all changes that were made, when they were made, and who made them. You can right click in any cell and select View History to see this information. You can use the the cell history to determine when a task was marked as complete.

Ramsay Zaki

It would be great if we could have a function for this (i.e. show the last modified date of a particular cell...not the entire row).

mmurphy14986

Used the first posted formula and it works like a charm.

Thank you!

Maxime Vanwynsberghe

yes, been looking for this for ages, haven't figured it out jet.

Any luck so far?

Hells Angel

Hi,

Does anyone got any luck. I need to put today's date when status is changed to completed but i don't want it to change everyday. But it is changing everyday.

I tried doing other way round that if the formula cell is empty and status is completed than pick current date and if not then let the date remain there. but it says circular reference error (because it is pointing to the same cell itself).