Forum Discussion

EricRovner's avatar
EricRovner
Qrew Member
3 years ago

Date Formula Assistance

Hello,

I'm once again reaching out to the amazing community of folks way smarter than me for some assistance creating a formula that is absolutely stumping me but probably child's play for the community wizards!

I've built an App for managing our fleet of vehicles - inclusive of Inspection Date tracking.

For the inspection date tracking, our trailers must be inspected twice each year and said inspections must be performed within specific date "windows". One inspection must occur each year between March 1 and April 30 and then performed again between August 1 and September 30. I need to create a formula that projects the next inspection date for a trailer by referencing the last inspection date. Let's call the two fields "Last Inspection" and "Next Inspection". Given that an inspection can occur on any date within the allowed "window" (March 1 and April 30 and then again between August 1 and September 30), I cannot simply add a fixed addition of days to the "Last Inspection" to populate the "Next Inspection" because it could fall outside of the next "window".

In other words, if a trailer's "Last Inspection" field is populated with a date between March 1 and April 30, the "Next Inspection" field must populate with a date between August 1 and September 30. For ease, it would be acceptable for the "Next Inspection" field to simply populate as August 1 in this case.

Same scenario if a trailer's "Last Inspection" field is populated with a date between August 1 and September 30, the "Next Inspection" field must populate with a date between March 1 and April 30. For ease, it would be acceptable for the "Next Inspection" field to simply populate as March 1 in this case.

Thanks in advance for any wisdom and assistance!

------------------------------
Eric Rovner
------------------------------
Formulas and functions
  • AhuvaBrown's avatar
    AhuvaBrown
    Qrew Cadet
    3 years ago
    I may be misunderstanding the issue, but is there a reason you can't add a fixed number of months to the last inspection date? Meaning, if the last inspection date is between August 1 and September 30, add 7 months to the date to get your next inspection date (using the AdjustMonth() formula), and if the last inspection date is between March 1 and April 30, add 5 months.

    ------------------------------
    Ahuva Brown
    ------------------------------
    • EricRovner's avatar
      EricRovner
      Qrew Member
      3 years ago
      Thanks so much for the note Ahuva - seems like a terrific solution! I'm unfortunately unfamiliar with the AdjustMonth() formula. Would you be able/willing to share a sample formula for my situation just as you described?

      "...if the last inspection date is between August 1 and September 30, add 7 months to the date to get your next inspection date (using the AdjustMonth() formula), and if the last inspection date is between March 1 and April 30, add 5 months."

      Many thanks!

      ------------------------------
      Eric Rovner
      ------------------------------
      • AhuvaBrown's avatar
        AhuvaBrown
        Qrew Cadet
        3 years ago
        Here's the formula I came up with, which should be done in a Formula-Date field:

        If(Month([Last Inspection Date]) = 3 or Month([Last Inspection Date]) = 4, AdjustMonth([Last Inspection Date], 5),
        Month([Last Inspection Date]) = 8 or Month([Last Inspection Date]) = 9, AdjustMonth([Last Inspection Date], 7))

        The Month([Last Inspection Date]) function retrieves the month number, so if the last inspection date is March, it will output 3, etc.

        If the month of the last inspection date is March or April (3 or 4),
        AdjustMonth([Last Inspection Date], 5) adds 5 months to the date. So if the last inspection date is March 1, 2022, the formula will result in August 1, 2022. 

        If the month of the last inspection date is August or September (8 or 9),
        AdjustMonth([Last Inspection Date], 7) adds 7 months to the date. So if the last inspection date is September 30, 2022, the formula will result in April 30, 2023. 

        Does that make sense?

        ------------------------------
        Ahuva Brown
        ------------------------------