Formula for last day in the month

System

This discussion was created from comments split from: Conditional Formatting.

Adilson Mota

Hello

Could you please help me identify the correct formula for last day in the month.

KPH

There is not a function to find the last day in the month but you can write a formula using the DATE function, to calculate it. Because months have different lengths (28, 29, 30, 31 days), we need to find the start date of the subsequent month (which is always 1) and subtract on day to find the end date.

For example, if you want to know the last day in the month based on a date in a cell called "Base Date" (formated as Date Type) your formula would be

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

If this formula is placed in another Date formatted column on the same row, it will return the last date of the month that Base Date is in.

It does it by creating a date:

• The year of which it takes from the Base Date cell. Let's say this was Jan 17th 2024. The year would be 2024.
• The month it also takes from the Base Date, and it adds 1 to it. So for Jan 17th the month in the new date is February.
• The day is always 1, i.e. Feb 1st.

It then subtracts one from this date, and that gives us the last day in January.

There is a slight complication in that you can't subtract 1 from the month of January to find December. 1-1 is not 12. So we add another little IF to return December 31st of the year in the Base Date cell, if the month is December.

= IF(MONTH([Base Date]@row) = 12, DATE(YEAR([Base Date]@row), 12, 31), DATE(YEAR([Base Date]@row), MONTH([Base Date]@row) + 1, 1) - 1))

You don't need to output these dates into columns, you can use them within formula. You also don't need to have a start date cell, you could use TODAY() to find the end date of the current month.

I hope this helps.

I noticed you had a couple of questions here that were not answered - I think they are being missed as they will not appear in the "unanswered questions" list. When asking questions in the community, please mark helpful answers as accepted and start new threads for new questions. This will prevent your subsequent questions from being missed (they look like follow-ups rather than new questions) and help other users with similar questions to find answers.