IF Percentage Complete = 100% return Date

This seemed simple to me before I started.. but this has me foxed..! two columns:

% Complete [Text/Number field]

Date Complete [Date field]

What I intended is for the Date Complete cell to automatically complete with TODAY() when the user updates the "% Complete" cell is set as 100%

..what I have so far is =IF([% Complete]17 < 100, 0, TODAY())

..and I have tried =IF([% Complete]17 = 100, TODAY())

Frustratingly, I can get TODAY() to return the Date when the "% Complete" isnt 100%.. but not when it's 100%

...any ideas..?

Find more posts tagged with

Formulas

Comments

Lewis Speak

Hello,

Try using this formula:

=IF([% Complete]1 = 1, TODAY(), "")

Feel free to enter a value between the last quotation marks for your 'Value if false' (the data that appears if the %Complete is not 100%).

As we are trying to capture the percentage, 100% is treated as the number 1.

You can find further information on using formulas here if required: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet

Let me know if that works for you.

Thank you

Lewis

%Complete.JPG

BobLonely

Thanks Lewis..

That's got it sorted..

Regards..

Simon

Andrée Starå

Hi Simon,

Be aware that the TODAY function would change to show the actual date when you open the sheet so the date won't be looked if that's what you need.

I hope this helps you!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Mike Wilday

To second Andree's statement there. The function Today() will always pull the current day's date every time the sheet is opened. That means, that your date of completion will always change to the most current day. You might want to consider flopping your formulas and make the Percentage column mark 100% when someone enters a date of completion. The challenge with that tho, is if someone sets a % manually, then the formula to fire the 100% would be overwritten. There currently isn't any timestamp functionality, but it would be welcome. Please submit a product enhancement request as many of us have desired this feature.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…