Need to include "Later Of" to a set normally calculates "Earlier Of"

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • In Progress
  • (Edited)
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])

Photo of Jennifer Peterson

Jennifer Peterson

  • 378 Points 250 badge 2x thumb

Posted 2 months ago

  • 0
  • 1
Photo of Forrest Parker

Forrest Parker

  • 872 Points 500 badge 2x thumb
Max() is the function you need.
Photo of Jennifer Peterson

Jennifer Peterson

  • 378 Points 250 badge 2x thumb
Problem is that the list of terms now includes both Min and Max
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.
"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?
Photo of Jennifer Peterson

Jennifer Peterson

  • 378 Points 250 badge 2x thumb
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 (MSO-NET)] , "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 (MSO-NET)] , "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.




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"
?
Photo of Jennifer Peterson

Jennifer Peterson

  • 378 Points 250 badge 2x thumb
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 ([MSO-NET - 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] ))


Photo of Jennifer Peterson

Jennifer Peterson

  • 378 Points 250 badge 2x thumb
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 ([MSO-NET - MSO Window Later]=true,
Max([MSO Delivery Deadline1], [MSO Delivery Deadline2]),
Min ([MSO Delivery Deadline1], [MSO Delivery Deadline2]))

MSO Delivery Deadline Calc1-2 Formula
If ([MSO-NET - 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 Calc1-2])

MSO Report Deadline Formula
Nz([MSO Delivery Deadline Calc2], [MSO Delivery Deadline Calc1])