Need help with a list column formula

[alandarr]

I could use a little help with a formula in a list if someone has a minute.

I have a column [% Complete] and another column [Date Completed].

[% Complete] is a number, and shown as a %.

I'd like to show the date in the [Date Complete] column, when someone eventually enters 100 in the [% Complete] column.

I've tried a few combinations but nothing has worked yet. Not really sure what I'm doing!

[% Complete] is set as a "number"

[Date Complete] is set as a "date and time"

Does this need to be done with a Flow?

[#2 · Accepted solution]

@alandarr 

You can use the flow to update the date very easily. Please look at the screenshot below

Hope it helps, please like it or mark it as a solution if it resolves ur clarification or issue

-Sudharsan K...

[#3]

@Sudharsan K 

Thanks for pointing me in the right direction. I have created the items as you have suggested.

There are a lot of options in the Update Item step! I have attempted to edit this, but nothing seems to be updating my list. Are the edits in my screen shot even remotely correct?

I also got a popup that said something about "after saving, don't forget to Create Flow". What is that about? I tried to add one more step (Create Flow) but had a bunch of things to fill in also.

I'm guessing this is very intuitive for many people, but I see no place online where all of these options are defined. I could "trial & error" until the end of days :(

Thanks for taking the time to help!

[#4]

Hi,
Please change the condition. Check for value '1' not '100'. SharePoint will consider from 0 to 1. In the update action just fill in the mandatory fields and date column that has to be updated and leave the other fields so that it won't get updated. Nothing else to be modified. Just try it and let me know.

[#5]

@Sudharsan K 

I changed the value back to "1".

For "If Yes", I chose the Update Item and by default, it looks like the following:

When I add the site address and the List Name, a whole bunch of other crap comes up:

I have tried to fill in the required fields, but there is no explanation for what to put in them! I tried the following:

The title of the item/column to append is definitely "Date Completed". As for the rest, I'm not even sure why it is needed.

Thanks!

[#6]

Hi,
The 'Update Item' action will load all the fields that are tied to the list selected. Just select the values from 'triggeraction' for the mandatory fields. For the 'Date Completed' field just map the 'Modified' field from the triggeraction.

[#7]

Yes, that worked!!!

Once the "% Complete" column has been updated to "100", the "Date Completed" column does show today's date in the correct row. I'm not sure why "100" works and "99" doesn't when the condition "is equal to" 1. Seems like it needs to be equal to 100.

If the number is changed back from 100 to 99, for example, the item still shows the date which has to be removed manually. I'll have to try and figure that one out.

Thank you so much for your help with this! Clearly, I have a lot to learn. I'll need to find some good resources however. The Microsoft examples really don't explain it very well.

[#8]

Hi,
If you want the condition to work for 99 & 100, then you have to change the condition to 'greater than equal to 0.9', as I already mention the values in SharePoint is calculated from 0 to 1.

[#9]

Your suggestion worked. I did not realize that 1 = 100. Clearly that is very important to remember!

Regarding the "99", I meant that if the number was switched back to something other than 100, the date remains. I would have to set up another condition I believe.

Thank you very much for your help! It works perfectly.

[#10]

Hi
Can you mark the reply as a solution. Other would benefit from this.

[#11]

@Sudharsan K 

Yes, I marked your first response. Thanks!

[#12]

This was a helpful thread, thank you!