I am trying to create a custom Task text field that displays what the forecasted or expected work should be by comparing the Forecasted Work with the Elapsed Duration (A custom duration field I created that calculates the working days that have passed since the actual start of the task up to the status date). This will help me compare the value from Actual Work to the Forecasted Work to determine if the task is falling behind based on actual work compared to how much work should be completed.
The formula I'm trying to use to calculate Forecasted Work is as follows, however I'm getting a syntax error for some reason. Also, it appears that the resource assignment units field assigned to the task doesn't seem to be available from the field selection options (maybe because I'm selecting a Task type field instead of resource type field option?), so this may not work, however if I change the custom field option to a resource type custom field the Max Units field is available.
Can anyone think of what might be wrong with the formula or an alternative way I can get the "Forecasted Work"?
IIf([Actual Start]<>"NA" AND [% Complete]<100 AND [Actual Work]<[Work],[Elapsed Duration] * [Max Units],0)&"hrs"