Insert a date as last day of the prior month

kmorsesmartsheet

Trying to create a formula to insert a date referencing a Date Submitted column from a referenced sheet. Date inserted on new sheet should be one month prior and the last day of the referenced sheet month. So if current date of Date Submitted reads 04/03/2025 the date on new sheet should read the prior months end of 03/31/2025. Any suggestions?

VBAGuru

If you make sure the column that this formula is going into is a Date column and the submitted date is a date column, this formula works:

=DATE(YEAR([Date Submitted]@row), MONTH([Date Submitted]@row), 1) - 1

Basically you are just setting the date to the first of the current month, then subtracting one day to get the last day of the previous month.

VBAGuru

Personally, I would add a column that you could hide on the reference sheet {Design Request Tracker} and insert that formula above and make it a column formula. Then on the new sheet do and Index/Match to pull in this date.

=Index({Design Request Tracker End of last month},Match([Identifier]@row,{Design Request Tracker Range that you can match}))

It would simplify the formulas

kmorsesmartsheet

That makes sense. Thank you for your help.

VBAGuru

If you make sure the column that this formula is going into is a Date column and the submitted date is a date column, this formula works:

=DATE(YEAR([Date Submitted]@row), MONTH([Date Submitted]@row), 1) - 1

Basically you are just setting the date to the first of the current month, then subtracting one day to get the last day of the previous month.

kmorsesmartsheet

Thank you. for your response, can you relate this to the referance sheet? here's what I have:

=DATE(YEAR({Design Request Tracker Range 1}, ISDATE(@cell ), MONTH({Design Request Tracker Range 1}, MONTH(@cell ),  1) - 1

VBAGuru

Personally, I would add a column that you could hide on the reference sheet {Design Request Tracker} and insert that formula above and make it a column formula. Then on the new sheet do and Index/Match to pull in this date.

=Index({Design Request Tracker End of last month},Match([Identifier]@row,{Design Request Tracker Range that you can match}))

It would simplify the formulas

kmorsesmartsheet

That makes sense. Thank you for your help.

VBAGuru

Glad I could help!