Expand all | Collapse all

Dates, Workdates and combinations in formulas...

  • 1.  Dates, Workdates and combinations in formulas...

    Posted 05-18-2018 12:08

    My tasks form is set up with a few date fields;

    estimated start [ ] estimated finish [ ] estimated duration [ ]
    actual start [ ] actual finish [ ] actual duration [ ]

    calculated end date [ ]

    These fields are set up like;

    estimated start [user entered] 

    estimated finish [estimated start + estimated duration] 

    estimated duration [user entered]

    actual start [user entered ] 

    actual finish [user entered] 

    actual duration [actual finish - actual start]

    calculated end date [if actual finish not null use that, if estimated finish not null use that, otherwise blank]

    In order to support predecessors I think the fields should be set as;

    estimated start [work date] 

    estimated finish [formula date]  

    estimated duration [duration]

    actual start [date ]  

    actual finish [date] 

    actual duration [formula duration]

    calculated end date [formula work date]

    But I'm running into all sorts of issues with the mix of dates and workdates.

    For example the formula in estimated finish is;

    WeekdayAdd([estimated start],[estimated duration])

    Hovering over the marked part of the formula ([estimated duration]) I get "Expecting number but found duration";

    Alrighty, so I need to make [estimated duration] into days so;

    WeekdayAdd([estimated start]),ToDays([estimated duration]))

    Then the whole formula is marked and I get "Expecting date but found workdate" ...and a headache!  I feel I've been through every iteration in formula and field type, but I know there must be one elusive combination that works out there.

    The reason for all this is to maintain estimated durations against actual durations for metrics whilst also providing updated dates for predecessors.

    There are some other formulas in this batch that are giving me problems, but I'll continue to try and puzzle them out if anyone can give me an idea where I'm going wrong in the above.

  • 2.  RE: Dates, Workdates and combinations in formulas...

    Posted 05-18-2018 12:14
    Hi Mark,

    I used to run into this issue. Make [estimated duration] a formula - numeric field and you should be good to go.