Discussions

Expand all | Collapse all

Formula combining workdates and dates (again...)

  • 1.  Formula combining workdates and dates (again...)

    Top
    Contributor
    Posted 05-23-2018 07:33

    Workdates continue to plague me... :)

    I have the following fields;

    [estimated start] [estimated end] [estimated duration]

    [actual start] [actual end] [actual duration]

    [calculated end date]

    Of which [estimated start] and [calculated end date] are work dates to support predecessors (the [calculated end date] feeds into the [estimated start] of the next task).  There are two formulas, one is for [estimated finish] and one is for [calculated end date]

    1. [estimated finish] = [estimated start] + [estimated duration]

    This one works fine, after changing [estimated duration] to a numeric (thanks to Gil for pointing that one out)

    2. Formula for [calculated end date] field I have is;

    If(

    not IsNull ([actual finish]), [actual finish],

    not IsNull ([estimated finish]), [estimated finish])

    What I want it to do is basically look at the [actual finish] field, if that is not blank then use the date in that field for the [calculated finish] date else look at the [estimated finish] field and use the date there (if both are blank then remain blank)

    I'm hoping I haven't totally borked the formula above but I'm getting a "expecting workdate but found date" error, if I adjust it to ToDate([actual finish]) I get a "expecting text/datetime/workdate but found date".

    I'm sure there is going to be an simple answer to this one that I will kick myself about later, but I'm missing it right now, could anyone suggest a way to do this?



  • 2.  RE: Formula combining workdates and dates (again...)

    Posted 05-23-2018 10:56
    Hi Mark, thank for being so explicit in your question.

    It should work I did something identic and it works , you can see any error message.


    Check that all the fields are Type Date. 

    ([actual finish], [actual finish],[estimated finish], [estimated finish]




  • 3.  RE: Formula combining workdates and dates (again...)

    Top
    Contributor
    Posted 05-23-2018 11:13

    Thanks for your reply, appreciated!

    I'm going to try your syntax since it's slightly different to mine so maybe I'm making a mistake there.

    One other problem is that I need to use workdate for the [estimated start] and the [calculated end date] so that I can use predecessors, did you not need to use predecessors?



  • 4.  RE: Formula combining workdates and dates (again...)

    Top
    Contributor
    Posted 05-24-2018 08:40

    To loop back on this one, the following field set up works for predecessors

    [estimated start] = work date
    [estimated finish] = formula - date (using end date formula builder)
    [estimated duration] = numeric

    [actual start] = date
    [actual end] = date
    [actual duration] = formula - duration

    [calculated end date] = formula - work date (as below)

    If(
    not IsNull ([actual finish]), [actual finish],
    not IsNull ([estimated finish]), [estimated finish])

    NOTE: the formula for calculated end date shows a red line and yellow highlight error at [actual finish] but still it works anyway.  Also, if editing this field it will sometimes pop up a syntax error but this isn't consistent and the formula works anyway.

    I need to start using this with actual data and see if any other issues pop up.

    Thanks to Esther above and Lovell from Quickbase who helped with this.