I have a set of formulas that calculate a date based on a list of terms in a field. Until now, the terms that had two parts were "the earlier of" As such, I compared the two results using the "Min" function in another formula. Now I have two new terms that are based on the "the later of" two dates, so the "Min" function won't work anymore.
Looking for suggestions.
Example of the comparison formula:
Min ([MSO Delivery Deadline1], [MSO Delivery Deadline2])
Looking for suggestions.
Example of the comparison formula:
Min ([MSO Delivery Deadline1], [MSO Delivery Deadline2])
 378 Points
Posted 2 months ago
Alex Molochko  now with 2019 Certification!
 2,592 Points
Can you write out in English the comparison you're trying to write? If Forrest's answer isn't the answer, I don't understand the question.
Alex Molochko  now with 2019 Certification!
 2,592 Points
"I was just given new terms that want me to calculate the "later of" date, in the same set of formulas."
Can you tell us anything about this? Later of what? Compared to what? How does this relate to the inputs and/or outputs of the first three formulas? What is the desired outcome?
Can you tell us anything about this? Later of what? Compared to what? How does this relate to the inputs and/or outputs of the first three formulas? What is the desired outcome?
 378 Points
The first term has always been part of the current formulas for the "earlier of," but the second one is a new term.
"The earlier of 2 years from 1st payment / 10 months from end of fieldwork"
"The later of 6 months from end of fieldwork / 24 months from 1st payment"
This is an abbreviated version of the 1st case statement formula field:
MSO Delivery Deadline1
Case ([Report Delivery Window  MSO (MSONET)] , "30 Days from when all relevant documents are received" , [Fieldwork End Date (AUD)] + Days(30) ,
"30 Days from end of FW" , [Fieldwork End Date (AUD)] + Days(30) ,
"60 days from end of fieldwork" , [Fieldwork End Date (AUD)] + Days(60) ,
"3 mo from leaving MSO office" , [Fieldwork Location Exit Date (AUD)] + Days(90) ,
"3 mo from leaving MSO office / 24 mos 1st pymnt" , [Fieldwork Location Exit Date (AUD)] + Days(90) ,
"3 mo from leaving MSO office / 27 mos 1st pymnt" , [Fieldwork Location Exit Date (AUD)] + Days(90) ,
"3 mo from leaving MSO office / 30 mos 1st pymnt" , [Fieldwork Location Exit Date (AUD)] + Days(90) )
The second case statement formula field, calculated the other half of the "or" terms:
MSO Delivery Deadline2
Case ([Report Delivery Window  MSO (MSONET)] , "3 mo from leaving MSO office / 24 mos 1st pymnt" , [Audit Scope Begins (AUD)] + Days(730) ,
"3 mo from leaving MSO office / 27 mos 1st pymnt" , [Audit Scope Begins (AUD)] + Days(840) ,
"3 mo from leaving MSO office / 30 mos 1st pymnt" , [Audit Scope Begins (AUD)] + Days(930) )
The third formula field is the one that returns the "earlier of" date:
MSO Delivery Deadline Calc1
Min ([MSO Delivery Deadline1], [MSO Delivery Deadline2])
These 3 fields are used to return the date for the final deadline.
"The earlier of 2 years from 1st payment / 10 months from end of fieldwork"
"The later of 6 months from end of fieldwork / 24 months from 1st payment"
This is an abbreviated version of the 1st case statement formula field:
MSO Delivery Deadline1
Case ([Report Delivery Window  MSO (MSONET)] , "30 Days from when all relevant documents are received" , [Fieldwork End Date (AUD)] + Days(30) ,
"30 Days from end of FW" , [Fieldwork End Date (AUD)] + Days(30) ,
"60 days from end of fieldwork" , [Fieldwork End Date (AUD)] + Days(60) ,
"3 mo from leaving MSO office" , [Fieldwork Location Exit Date (AUD)] + Days(90) ,
"3 mo from leaving MSO office / 24 mos 1st pymnt" , [Fieldwork Location Exit Date (AUD)] + Days(90) ,
"3 mo from leaving MSO office / 27 mos 1st pymnt" , [Fieldwork Location Exit Date (AUD)] + Days(90) ,
"3 mo from leaving MSO office / 30 mos 1st pymnt" , [Fieldwork Location Exit Date (AUD)] + Days(90) )
The second case statement formula field, calculated the other half of the "or" terms:
MSO Delivery Deadline2
Case ([Report Delivery Window  MSO (MSONET)] , "3 mo from leaving MSO office / 24 mos 1st pymnt" , [Audit Scope Begins (AUD)] + Days(730) ,
"3 mo from leaving MSO office / 27 mos 1st pymnt" , [Audit Scope Begins (AUD)] + Days(840) ,
"3 mo from leaving MSO office / 30 mos 1st pymnt" , [Audit Scope Begins (AUD)] + Days(930) )
The third formula field is the one that returns the "earlier of" date:
MSO Delivery Deadline Calc1
Min ([MSO Delivery Deadline1], [MSO Delivery Deadline2])
These 3 fields are used to return the date for the final deadline.
Alex Molochko  now with 2019 Certification!
 2,592 Points
I think I'm getting closer. Are you saying that, right now, you have three fields as you've described, to answer the question:
"The earlier of 2 years from 1st payment / 10 months from end of fieldwork"
With a single output?
And now you need to start from scratch to answer the question:
"The later of 6 months from end of fieldwork / 24 months from 1st payment"
With a separate output, based on a separate calculation, independent of everything you've shown so far?
IS there a relationship between
"The earlier of 2 years from 1st payment / 10 months from end of fieldwork"
and
"The later of 6 months from end of fieldwork / 24 months from 1st payment"
?
"The earlier of 2 years from 1st payment / 10 months from end of fieldwork"
With a single output?
And now you need to start from scratch to answer the question:
"The later of 6 months from end of fieldwork / 24 months from 1st payment"
With a separate output, based on a separate calculation, independent of everything you've shown so far?
IS there a relationship between
"The earlier of 2 years from 1st payment / 10 months from end of fieldwork"
and
"The later of 6 months from end of fieldwork / 24 months from 1st payment"
?
 378 Points
I found a solution to part of the problem. I created a checkbox formula that shows true when the term contains "Later" in the field. And then changed the first calculation to the following:
If ([MSONET  MSO Window Later]=true, Max([MSO Delivery Deadline1], [MSO Delivery Deadline2]), Min ([MSO Delivery Deadline1], [MSO Delivery Deadline2]))
Now I need to figure out how to apply the same logic to the second calculation:
If (not IsNull([Fieldwork Ends (AUR Specific)]) , Min ( [MSO Delivery Deadline3] , [MSO Delivery Deadline4] ))
If ([MSONET  MSO Window Later]=true, Max([MSO Delivery Deadline1], [MSO Delivery Deadline2]), Min ([MSO Delivery Deadline1], [MSO Delivery Deadline2]))
Now I need to figure out how to apply the same logic to the second calculation:
If (not IsNull([Fieldwork Ends (AUR Specific)]) , Min ( [MSO Delivery Deadline3] , [MSO Delivery Deadline4] ))
 378 Points
Final solution, adding another calculation field to handle the Min/Max question, and then returning that result if the "not IsNull" was true.
MSO Delivery Deadline Calc1 Formula
If ([MSONET  MSO Window Later]=true,
Max([MSO Delivery Deadline1], [MSO Delivery Deadline2]),
Min ([MSO Delivery Deadline1], [MSO Delivery Deadline2]))
MSO Delivery Deadline Calc12 Formula
If ([MSONET  MSO Window Later]=true,
Max([MSO Delivery Deadline3], [MSO Delivery Deadline4]),
Min ([MSO Delivery Deadline3], [MSO Delivery Deadline4]))
MSO Delivery Deadline Calc2 Formula
If (not IsNull([Fieldwork Ends (AUR Specific)]) , [MSO Delivery Deadline Calc12])
MSO Report Deadline Formula
Nz([MSO Delivery Deadline Calc2], [MSO Delivery Deadline Calc1])
MSO Delivery Deadline Calc1 Formula
If ([MSONET  MSO Window Later]=true,
Max([MSO Delivery Deadline1], [MSO Delivery Deadline2]),
Min ([MSO Delivery Deadline1], [MSO Delivery Deadline2]))
MSO Delivery Deadline Calc12 Formula
If ([MSONET  MSO Window Later]=true,
Max([MSO Delivery Deadline3], [MSO Delivery Deadline4]),
Min ([MSO Delivery Deadline3], [MSO Delivery Deadline4]))
MSO Delivery Deadline Calc2 Formula
If (not IsNull([Fieldwork Ends (AUR Specific)]) , [MSO Delivery Deadline Calc12])
MSO Report Deadline Formula
Nz([MSO Delivery Deadline Calc2], [MSO Delivery Deadline Calc1])
Related Categories

Formulas & functions
 2882 Conversations
 71 Followers
Jennifer Peterson