Complex IF - help needed

[Rafal_Bain]

Hi everyone,

I'm trying to create buckets for tasks with IF statement, but for some reason I'm getting wrong results. Does anyone see any aparent flaw in this calculation:

IF(Planned Hours>5,">5h",IF(Planned Hours>3,"(3-5h]",IF(Planned Hours>1,"(1h-3h]","[0-1h]")))

any help will be appreciated

R

[RandyRoberts]

Something like this should work fine:

valueexpression=IF({workRequired}>300,"5h plus",IF({workRequired}>180,"3-5h",IF({workRequired}>60,"1-3h","0-1h")))

Planned Hours=workRequired and is calculated in minutes. The API explorer is your friend.

[Rafal_Bain]

Thanks Randy,

your formula works perfectly fine on the report, I did test this.

However, I'm trying to implement mine as calculated custom field and here it doesn't, do you have any idea why it might be the case?

also what is workRequired and why do you use it instead of Planned hours?

[RandyRoberts · Accepted solution]

Calc fields are coded slightly different than report columns. The main difference is in the field naming convention. In the API, under the Task object, you can see "Planned Hours" equals "workRequired". In creating calc fields, the usual would be to use the simple name on the left. While in a report, you would use the API code name on the right in curly brackets {workRequired}.

I haven't trried this in a calc field but I think a good starting point would be to try a single IF and make sure that works first. Then you can add a second and third. I would dump the brackets from your "then" replies too. Instead of:

IF(Planned Hours>1,"(1h-3h]","[0-1h]")

I would start with:

IF(Planned Hours>60,"1h-3h","0-1h")

In the calc field creator, there is a syntax coloured editor with a built in wizard that allows you to select items from a dropdown. It is quite good and I highly recommend it. If you need to tweak code after you've selected as much as you can from the wizard you can. The wizard will gewt you most of the way there though.

Remember, if you're going to do this in a calc field, all tasks have to have the form attached with the field on it and it has to be a tasks form. This will not work on a project form as tasks would be a collection, not an object.

[Rafal_Bain]

Hi Randy,

so essentially problem was wit the units, converting mine PH>1 to PH>60 did the trick. Calculation works as expected.

Thanks so much!

R

[RandyRoberts]