Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
9 years ago

Deadline Date Formula

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?
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    9 years ago
    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.
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    9 years ago
    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.