Formula for 1st of the month in a prior month is coming up Invalid Value

Kasey Mabary

I have 3 different formulas for 1st of the month 1 month, 2 months and 3 months before renewal date, excluding holidays and weekends. The formula is working great, until it has to calculate back in a previous year.

1st Follow-Up Email Due Formula: =WORKDAY(DATE(YEAR([Renewal Date]@row), MONTH([Renewal Date]@row) - 2, 1) - 1, 1, Holidays:Holidays)

For 01/01/2026 renewal date this should produce 10/01/2025, but it gives an error message of "Invalid Value"

I've noticed that the ones that produce "Invalid Value" should be displaying a previous years date for the output date, in relation to the Renewal Date. Is there a way to fix this?

Paul Newcome

I use an IFERROR around my DATE function to output a different DATE function.

=WORKDAY(IFERROR(DATE(YEAR([Renewal Date]@row), MONTH([Renewal Date]@row) - 2, 1), DATE(YEAR([Renewal Date]@row) - 1, MONTH([Renewal Date]@row) + 10, 1)) - 1, 1, Holidays:Holidays)

Paul Newcome

@John_Foster The key to the above is that absolute values of the numbers added/subtracted in both of the month functions must add up to 12 and you flip the sign.

So going back two months is -2. That means the second one is +10. 2+10 = 12 and you flip the sign. Going back three months would be -3 and +9. Going forward 8 months would be +8 and -4. So on and so forth (and don't forget to adjust the year in the second one either).

This only works for less than 12 months in either direction. 12 months would be just a +/- 1 year with no month adjustment. Beyond 12 months in either direction requires a much more complicated formula. Excel uses EDATE. SS doesn't have that function, but I was able to write a formula for it. You should be able to locate it if you search for EDATE here in the Community.

John_Foster

Hi @Kasey Mabary,

I had a similar issue in one of my sheets and I added a nested if statement that selected a specific month if the date I was checking has month value 1, 2 or 3.

The other option you could use, and I have also used this, is a second sheet which has a lookup table with the various months based on the renewal date.

I would be happy to talk through either in more detail if it would help.

Paul Newcome

I use an IFERROR around my DATE function to output a different DATE function.

=WORKDAY(IFERROR(DATE(YEAR([Renewal Date]@row), MONTH([Renewal Date]@row) - 2, 1), DATE(YEAR([Renewal Date]@row) - 1, MONTH([Renewal Date]@row) + 10, 1)) - 1, 1, Holidays:Holidays)

John_Foster

@Paul Newcome that is a very clever solution to the problem, I am going to go back through and review my formulas based on that now.

Thanks,
John

Paul Newcome

@John_Foster The key to the above is that absolute values of the numbers added/subtracted in both of the month functions must add up to 12 and you flip the sign.

So going back two months is -2. That means the second one is +10. 2+10 = 12 and you flip the sign. Going back three months would be -3 and +9. Going forward 8 months would be +8 and -4. So on and so forth (and don't forget to adjust the year in the second one either).

This only works for less than 12 months in either direction. 12 months would be just a +/- 1 year with no month adjustment. Beyond 12 months in either direction requires a much more complicated formula. Excel uses EDATE. SS doesn't have that function, but I was able to write a formula for it. You should be able to locate it if you search for EDATE here in the Community.

John_Foster

Thanks Paul, the additional information will be of use in some future projects I have and have already amended a couple of sheets that were using a way more complex formula.

Paul Newcome

@John_Foster Happy to help. 👍️