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

Leslye Jackson

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

Leslye Jackson

@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%", "")

Genevieve P.

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:

• Quick Video on Formulas & Formula Basics
• Referencing Columns in Formulas
• Using @row to look only within that row
• Formula Webinar Series

Let me know if you need any additional help!

Cheers,

Genevieve

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!

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

Leslye Jackson

@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%", "")