Deadline Date Formula

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
  • (Edited)
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?
Photo of Jennifer Peterson

Jennifer Peterson

  • 296 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
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.
Photo of Jennifer Peterson

Jennifer Peterson

  • 296 Points 250 badge 2x thumb
Would that change other formulas calculating off of this field?
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.