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 comm...
Forum Discussion
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
------------------------------
------------------------------
Ahuva Brown
------------------------------
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
------------------------------
"...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
------------------------------
- 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
------------------------------- 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
------------------------------
