Automate (100%) in % Completes column based on date entered in Complete Date column

Leslye Jackson
Leslye Jackson ✭✭
in Formulas and Functions

HAPPY FRIDAY EVERYONE

All, I need some help with this formula

The goal: when a Complete Date is entered the % Complete Column will update to 100%

The results have been unfavorable


=ISDATE([Complete Date]45),([% Complete])="100%") This one gives an unparsable

=IF(ISDATE([% Completes]45), "100%") This one doesn't give an error but also does not give any results


image.png


· Share on FacebookShare on Twitter

Best Answer

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Leslye Jackson

    First, you'll want to make sure this formula is pasted into the column where you want the result to appear (your % complete column). You will also want to reference the Complete Date column as the one that has a Date in it, instead of the % Complete column (which has text).

    Secondly, if nothing is appearing for your second formula, it means that the information in the ISDATE(reference) is not being read as a Date. Since you're referencing the % Complete column instead of the Date column, this wouldn't be able to find a date and so it would return blank.

    Try this instead, pasted into your % Completes column:

    =IF([Complete Date]@row <> "", "100%", "In Progress")


    This will return the text "100%" in your % Complete column, but only if the Complete Date column for that row is not blank (<> means "not", and "" means "blank")

    Keep in mind that if you're using a formula for a column you won't want to then update the column manually. Any manual changes to a cell that has a formula will erase the formula... it's either automatically adjusted or you can manually change the percent. Does that make sense?

    Here are some Help Center articles that may help as you work with formulas:


    Let me know if you need any additional help!

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Leslye Jackson

    =IF(ISDATE([Complete Date]@row), 100%,"") in your [% Completes] column should be working.

    On your first formula the part after the comma is what makes it unparseable. On the second formula, you're testing the [% Completes] column to check if it's a date. But according to your screenshots, it's a dropdown list. So it's never a Date and thus returning nothing...

    Now your [Complete Date] column is locked. Users that aren't owners or administrators of the sheet won't be able to edit this cell to put the end date. Maybe it's done on purpose from your side but I thought it was worth noting.

    Hope it helped!

    · Share on FacebookShare on Twitter
  • Leslye Jackson
    Leslye Jackson ✭✭

    @Genevieve P Thank you so much for your feedback on this one!!


    So I tried that and these were my results. The goal is to have the Complete date update to whatever date the 100% is checked. So lets say I checked that the task was complete yesterday at 100% I would want the Complete date to have updated 8/30/2020.

    Initially I was using the Today function but that did not work because the date would change everyday. I am trying different things to get it to work but thought I would reach out for some guidance.


    The % Complete column will be limited to 100% 75% 50% 25% choices, so as not to make things difficult for the client.

    I just need to tell the Complete Date Column to display the date a task goes into 100% Complete. I enjoy trying though even when things fail its all really coming together :)

    image.png


    · Share on FacebookShare on Twitter
  • Leslye Jackson
    Leslye Jackson ✭✭
    Answer ✓

    @David Joyeuse

    It worked the one in bold, we had to throw parenthesis around 100% . THANK YOU UBER MUCHHHHH!!!

    =IF(ISDATE([Complete Date]@row),100%,"")

    =IF(ISDATE([Complete Date]@row), "100%", "")

    image.png


    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!