Date formula

[Sirod]

Hello,

I'm calculating a proration and am stumped on a formula that result in a specific date. In cell A1 the user will enter the first day of the calendar year, i.e. Jan 1 2022. In cell B1 I need a formula so that the result is May 31 of the previous calendar year, i.e. May 31, 2021.

I've tried the formula =EDATE(A1,-7) which results in June 1 of the previous year. What am I missing?

Thank you,
S

 

[Joe4]

Just subtract one from your formula:

=EDATE(A1,-7)-1

 

[Sirod]

Just subtract one from your formula:

=EDATE(A1,-7)-1

Oh my. The amount of time that I spent on this..... such a simple solution. Thank you so much!

 

[Joe4]

Oh my. The amount of time that I spent on this..... such a simple solution. Thank you so much!

You are welcome.

Yes, your original formula goes back exactly 7 months from Jan 1, which will return June 1.
So subtracting one from that takes you back to the day before June 1.