Discussions

 View Only
  • 1.  Date Formula Assistance

    Posted 09-20-2022 15:17
    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
    ------------------------------


  • 2.  RE: Date Formula Assistance

    Posted 09-21-2022 12:41
    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
    ------------------------------



  • 3.  RE: Date Formula Assistance

    Posted 09-21-2022 13:07
    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
    ------------------------------



  • 4.  RE: Date Formula Assistance

    Posted 09-21-2022 13:31
    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
    ------------------------------



  • 5.  RE: Date Formula Assistance

    Posted 09-21-2022 13:58
    Brilliant! I just added this date-formula field and it works like a charm. Thanks so much for your helpful explanation and assistance Ahuva, I truly appreciate it!
    -Eric

    ------------------------------
    Eric Rovner
    ------------------------------