Custom Calculated Field to Identify if a Task is Past Due

[MelissaAv1]

We are working on a go-live task report, and we'd like to use the conditional formatting available in the report builder to turn an entire task row a different color if the planned completion date of the task is less than today and the status isn't closed. However, this isn't possible based on current functionality. So, we have created a custom calculated field to perform the calculation as follows: IF(Planned Completion Date<$$TODAY,IFIN(Status,'NEW','INP','ONH',"Yes","No")) We can get it to work for those tasks that are truly overdue and it displays 'Yes', but those that aren't display a blank. I'm sure that we're missing a simple thing, but cannot figure it out. Can anyone else spot it? Thanks for your help!

[MelindaLa1]

The conditional formatting executes sequentially. So if your first condition is if closed then white, then your second condition can be if due < today then red, third if due < today+2 then yellow, else green On Tue, Aug 23, 2016, 12:12 PM Melissa Averkamp < reportingforum@communitylists.workfront.com> wrote: > We are working on a go-live task report, and we'd like to use the > conditional formatting available in the report builder to turn an entire > task row a different color if the planned completion date of the task is > less than today and the status isn't closed. However, this isn't possible > based on current functionality. So, we have created a custom calculated > field to perform the calculation as follows: > > IF(Planned Completion > Date<$$TODAY,IFIN(Status,'NEW','INP','ONH',"Yes","No")) > > We can get it to work for those tasks that are truly overdue and it > displays 'Yes', but those that aren't display a blank. I'm sure that we're > missing a simple thing, but cannot figure it out. > > Can anyone else spot it? > > Thanks for your help! > > -----End Original Message----- >

[MelindaLa1]

But for the formula, add another ,"No" between the 2 closing parenthesis, so if the planned completion date isn't less then today, "No") IF(Planned Completion Date<$$TODAY,IFIN(Status,'NEW','INP','ONH',"Yes","No (Closed)"),"No (Current)") On Tue, Aug 23, 2016 at 12:41 PM Melinda Layten < reportingforum@communitylists.workfront.com> wrote: > The conditional formatting executes sequentially. > So if your first condition is if closed then white, then your second > condition can be if due < today then red, third if due < today+2 then > yellow, else green > > On Tue, Aug 23, 2016, 12:12 PM Melissa Averkamp < > reportingforum@communitylists.workfront.com> wrote: > >> We are working on a go-live task report, and we'd like to use the >> conditional formatting available in the report builder to turn an entire >> task row a different color if the planned completion date of the task is >> less than today and the status isn't closed. However, this isn't possible >> based on current functionality. So, we have created a custom calculated >> field to perform the calculation as follows: >> >> IF(Planned Completion >> Date<$$TODAY,IFIN(Status,'NEW','INP','ONH',"Yes","No")) >> >> We can get it to work for those tasks that are truly overdue and it >> displays 'Yes', but those that aren't display a blank. I'm sure that we're >> missing a simple thing, but cannot figure it out. >> >> Can anyone else spot it? >> >> Thanks for your help! >> >> -----End Original Message----- >

[MelissaAv1]

Hi Melinda, I tried that last week, and it keeps telling me that my expression is invalid, so I cannot save it. But, here's the funny thing. I just removed the 'IN' after the second 'IF', saved and closed the form. Next, I reopened it and added back in the 'IN' and it liked it. And now it displays properly. Do you (or anyone else for that matter) know why the expression builder doesn't work properly and accept things that are obviously correct? Either way, thanks for your help! Mel

[MelindaLa1]

I have had nightmare scenarios with the expression builder, I always keep a copy of all my calculations in another location and/or in the description of the field for calculated fields. Generally if the builder isn't accepting what I know should be valid, I copy everything out, and start pasting it back in from the middle out saving each step. I also like to add in extra descriptive titles to my ifs while building so I can test that everything is hitting the right answer. (Like adding the comments to the 2 no's so I know which no is hitting) There are actually some pretty significant differences between how Workfront presents expressions in the builder vs kickstarting or loading the expressions via the API or a custom form upload from AtAppStore. They are mainly the difference in how fields are named, the whole DE: and camel case vs natural language. And how the object dot language is used. I know Doug Dan Hoed has had many nightmares occur and tends to do his form editing in excel and imports the data in. On Tue, Aug 23, 2016 at 1:08 PM Melissa Averkamp < reportingforum@communitylists.workfront.com> wrote: > Hi Melinda, > > I tried that last week, and it keeps telling me that my expression is > invalid, so I cannot save it. But, here's the funny thing. I just removed > the 'IN' after the second 'IF', saved and closed the form. Next, I > reopened it and added back in the 'IN' and it liked it. And now it > displays properly. Do you (or anyone else for that matter) know why the > expression builder doesn't work properly and accept things that are > obviously correct? Either way, thanks for your help! > > Mel > > -----End Original Message----- >