Did a date formula change?

MichelleBohn

=DATE(YEAR([Last Month]@row) - 1, MONTH([Last Month]@row), 1)

=DATE(YEAR([Current Month]@row), MONTH([Current Month]@row) - 1, 1)

I have been using this above formula for the last 6+ months in 20+ sheets but I noticed it is not working anymore. Did something change or do I need to update something since the year changed?

Samuel Mueller

@MichelleBohn I believe this will get you there. should only need it in the "last month" column

=DATE(YEAR([Current Month]@row), if(MONTH([Current Month]@row) - 1 = 0, 12 ,MONTH([Current Month]@row) - 1), 1)

Samuel Mueller

@MichelleBohn since the month of January is 1 you cannot subtract 1 and have it know that you mean December. You have to write an if state to account that if month = 1, return 12.

What your formula returns is date(2023,0,1) which is causing the errors.

MichelleBohn

Thank you so much Samuel, this makes sense now, I appreciate it!

Samuel Mueller

@MichelleBohn happy to help. Let me know if you are having trouble with the IF formula.

MichelleBohn

Samuel, I thought I had it but if you don't mind can you assist with the IF formula I would greatly appriciate it. Thank you

Samuel Mueller

@MichelleBohn I believe this will get you there. should only need it in the "last month" column

=DATE(YEAR([Current Month]@row), if(MONTH([Current Month]@row) - 1 = 0, 12 ,MONTH([Current Month]@row) - 1), 1)

MichelleBohn

Thanks again for the speedy responses. This worked only issue is that it is reading the current year so it puts it at 12/1/23 instead of 12/1/22 as last month. I'm going to manually update for now, thanks again for all the help. I was putting the If statement at the end which is why it wasn't working right for me.

Samuel Mueller

@MichelleB Oh you're right, here

=DATE(if(MONTH([Current Month]@row) - 1 = 0, YEAR([Current Month]@row)-1,YEAR([Current Month]@row)) , if(MONTH([Current Month]@row) - 1 = 0, 12 ,MONTH([Current Month]@row) - 1), 1)

MichelleBohn

You're a lifesaver! You are incredible with formulas. I have to use google and watch youtube all the time to figure out formulas. Thank you this worked perfectly.

Samuel Mueller

Happy to help! 😀