Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
7 years ago

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

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])

Formulas and functions
    • ArchiveUser's avatar
      ArchiveUser
      Qrew Captain
      7 years ago
      Problem is that the list of terms now includes both Min and Max
  • AlexCertificati's avatar
    AlexCertificati
    Qrew Cadet
    7 years ago
    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.
    • ArchiveUser's avatar
      ArchiveUser
      Qrew Captain
      7 years ago
      So, I have over 60 different terms listed in a field. These are the examples of the 3 types of terms (single, earlier of, later of)

      180 days from end of FW
      earlier of 6 mos end of FW / 9 mos from beginning of FW
      later of 6 mos end of FW / 24 mos from 1st pymt

      I have a case statement formula calculating the single & 1st date ([MSO Delivery Deadline1]), and another one to calculate the 2nd date ([MSO Delivery Deadline2]).

      Then I have a 3rd formula to compare the 2, returning the earlier of the dates.

      Min ([MSO Delivery Deadline1], [MSO Delivery Deadline2])

      My problem is that they just gave me new terms that are "the later of" now. Not sure how to calculate both in the same formula.



    • AlexCertificati's avatar
      AlexCertificati
      Qrew Cadet
      7 years ago
      I'm sorry, I don't understand what you are trying to do.

      Everything up to 'My problem is' in your comment is fine. But I don't see where you describe the comparison you're trying to write. Again, you have the Min() correctly to calculate the second date, and to calculate the third date you need a Max() for that; it seems that you understand that.

      What do you mean by 'calculate both in the same formula'? Can you write out in English the entire comparison you need to do? You calculate the first date, calculate the second date, then you compare the two, then... what?
    • ArchiveUser's avatar
      ArchiveUser
      Qrew Captain
      7 years ago
      Currently, it calculates like this...
      *1st Formula Field: If the term is X, calculate the single term or the 1st half date for [MSO Delivery Deadline1]
      2nd Formula Field: If the term is X (only if it contains an "earlier of" term, calculate the 2nd half date for [MSO Delivery Deadline2]
      3rd Formula Field: Compare [MSO Delivery Deadline1] & [MSO Delivery Deadline2], and return the earlier date.

      I was just given new terms that want me to calculate the "later of" date, in the same set of formulas. Currently, my comparison only allows for the "earlier of" result, and I'm not sure how to have those new terms calculate on the "later of" result.

      *My 1st formula field is over 60 lines long, or I'd paste it in here.