Help with calculated field on a task form

[KellieGardner]

I need a report to capture the % complete of a task as of certain dates.

I've done this previously by creating a calculated custom field on a task custom form using this code: IF(ISBLANK({% complete as of 9/30/22}),{percentComplete},{% complete as of 9/30/22})

This worked great, until today when I noticed that when it was being added to new tasks it was calculating the percent complete as of the day the task form was added to the task.

I need to do this same scenario again so that I can capture the % complete as of today, 3/1/23. How can I modify this code so that it will get the data for today but not for a future task entered after today? Maybe I've been staring at it too long but I can't seem to figure out how to get it to not capture tasks created after today 

[Heather_Kulbacki · Accepted solution]

Hi @KellieGardner 

Your formula is basically saying "if my field is blank grab that percent complete and add it in front of the '% complete of of 9/30/22' text, if it's not blank leave it as is"

so any time the form that field is on is added to a task, that field is going to be blank and get filled in with the current percent complete and the exact text you've indicated.

Not sure if I've got the syntax correct, but you might try a new field named something like "% Complete As Of" then a formula like:

IF(ISBLANK({% Complete As Of}),CONCAT({percentComplete}" as of "$$TODAY),{% Complete As Of})

saying: "if my field is blank grab that percent complete add the text 'as of' then today's date, if it's not blank leave it as is"

**it's the syntax of $$TODAY that I'm most unsure of - not sure if you need some type of brackets around that.