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

Options
Devan Morin
Devan Morin ✭
edited 12/09/19 in Archived 2015 Posts

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?

Tags:
· Share on FacebookShare on Twitter

Comments

  • Stephen Uekert
    Stephen Uekert ✭
    Options

    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

    · Share on FacebookShare on Twitter
  • Devan Morin
    Devan Morin ✭
    edited 09/11/15
    Options

    Hi Steve,

     

    I figured I must have been inputing the formula incorrectly.

     

    Thanks a million for the help! 

     

    - Devan

    · Share on FacebookShare on Twitter
  • Stephen Uekert
    Stephen Uekert ✭
    Options

    No problem. Glad I could help!

    · Share on FacebookShare on Twitter
  • Chris Goodman
    Chris Goodman ✭
    edited 11/30/15
    Options

    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

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee
    Options

    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.

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee
    Options

    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.

    · Share on FacebookShare on Twitter
  • Devan Morin
    Devan Morin ✭
    Options

    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!

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee
    Options

    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. 

    · Share on FacebookShare on Twitter
  • Ramsay Zaki
    Ramsay Zaki ✭✭✭✭✭✭
    Options

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

    · Share on FacebookShare on Twitter
  • mmurphy14986
    mmurphy14986 ✭
    Options

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

     

    Thank you!

    · Share on FacebookShare on Twitter
  • Maxime Vanwynsberghe
    Maxime Vanwynsberghe ✭
    Options

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

    Any luck so far?

    · Share on FacebookShare on Twitter
  • Hells Angel
    Hells Angel ✭
    Options

    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,

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories