Discussions

Expand all | Collapse all

Deadline Date Formula

  • 1.  Deadline Date Formula

    Top
    Contributor
    Posted 03-20-2017 22:47
    I am trying to return some kind of value other than a date when the contract term is no contract. This is a list of my current contract terms:

    30 Days from when all relevant documents are received
    6 weeks from end of FW
    8 weeks from end of FW
    8 weeks from end of FW / no later than 12 weeks from end of FW
    60 days from end of FW
    9 weeks from end of FW
    90 days from end of FW
    3 mo from end of FW
    12 weeks from end of FW
    15 Weeks from End of Fieldwork
    120 days from end of FW
    6 mo from beginning/no later than 3 mo end
    6 mo from beginning/no later than 6 mo end
    6 Months from End of Fieldwork
    No Window &/or Contract

    I have all the calculations for the terms based on dates, but I want the last term (No Window &/or Contract) to return some kind of null value to let my users know in the deadline field that there is nothing to report. This is my current formula:

    Case ([Report Delivery Window - MAI] ,
    "30 Days from when all relevant documents are received" , [Fieldwork End Date (AUD)] + Days(30) , 
    "6 weeks from end of FW" , [Fieldwork End Date (AUD)] + Weeks(6) ,
    "8 weeks from end of FW" , [Fieldwork End Date (AUD)] + Weeks(8) ,
    "8 weeks from end of FW / no later than 12 weeks from end of FW" , [Fieldwork End Date (AUD)] + Weeks(8) ,
    "60 days from end of FW" , [Fieldwork End Date (AUD)] + Days(60) ,
    "9 weeks from end of FW" , [Fieldwork End Date (AUD)] + Weeks(9) ,
    "90 days from end of FW" , [Fieldwork End Date (AUD)] + Days(90) ,
    "3 mo from end of FW" , [Fieldwork End Date (AUD)] + Days(90) ,
    "12 weeks from end of FW" , [Fieldwork End Date (AUD)] + Weeks(12) ,
    "15 Weeks from End of Fieldwork" , [Fieldwork End Date (AUD)] + Weeks(15) ,
    "120 days from end of FW" , [Fieldwork End Date (AUD)] + Days(120) ,
    "6 mo from beginning/no later than 3 mo end" , [Fieldwork Begin Date (AUD)] + Days(90) ,
    "6 mo from beginning/no later than 6 mo end" , [Fieldwork Begin Date (AUD)] + Days(180) ,
    "6 Months from End of Fieldwork" , [Fieldwork End Date (AUD)] + Days(180) )

    Any suggestions?


  • 2.  RE: Deadline Date Formula

    Bronze
    Contributor
    Posted 03-20-2017 23:03
    you could use: "No Window &/or Contract", null

    that would leave the date field blank. If you want something other than that, you might change this to a formula text field so that you could do something like: "No Window &/or Contract" , "Nothing to Report"

    if you go with the formula text, you would need to add totext( ) around each line. 

    let me know if that helps.


  • 3.  RE: Deadline Date Formula

    Top
    Contributor
    Posted 03-20-2017 23:07
    Would that change other formulas calculating off of this field?


  • 4.  RE: Deadline Date Formula

    Bronze
    Contributor
    Posted 03-20-2017 23:09
    yea... so maybe the formula text isn't the best idea.

    just fyi - date fields like to have either a date value, or a null value. that's all you have to work with.