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.
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?
Comments
-
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
-
Hi Steve,
I figured I must have been inputing the formula incorrectly.
Thanks a million for the help!
- Devan
-
No problem. Glad I could help!
-
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
-
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.
-
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.
-
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!
-
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.
-
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).
-
Used the first posted formula and it works like a charm.
Thank you!
-
yes, been looking for this for ages, haven't figured it out jet.
Any luck so far?
-
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).
Any help would be highly appreciated. This feature is a must need.
Thanks,
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives