How to create a formula to show last day of the month

Ben Jackson

I am trying to create a monthly timeline template, where once I set the start date for the project, the 'Billing Month 1 start' column collects the date and the following column 'Billing month 1 Close' column indicates the end of that month.

From there

The following 'Valuation 1' column is the result of the prorate

then;

'Billing Month 2 start' = 'Billing Month 1 Close + 1

'Billing Month 2 Close' = end of that month

etc......

Possible???

Thanks

Paul Newcome

The easiest way to get the last day of the month is to get the first day of the next month and then subtract 1.

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

Ben Jackson

Paul,

awesome! thank you

Paul Newcome

The easiest way to get the last day of the month is to get the first day of the next month and then subtract 1.

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

Ben Jackson

Paul,

awesome! thank you

Paul Newcome

Happy to help. 👍️

Allison Horn

@Paul Newcome - How would this formula be applied for the LAST DAY of the PREVIOUS MONTH?

I have the below, but obviously, this is populating the last day of the current month. I am trying to find a way to show our Operators the last day of the previous month so it's abundantly clear what report parameters to run.

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1

Paul Newcome

@Allison Horn You would use something more like this:

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1

Allison Horn

@Paul Newcome - one more on this. the formula worked great until the last day of the prior year. Is there a way to take the below formula and add an if error statement that says basically give me the last day of the prior month EXCEPT if it's December 31 and have it read "null" for December 31st?

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1

Paul Newcome

@Allison Horn Did it come up with an error? Did it work on Dec. 30th?

The way the formula works is we are basically outputting the first day of the current month

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

Then subtracting one day from it which should give us the last day of the previous month regardless of year overlap or anything like that.

Allison Horn

The formula works perfectly; however, I am trying to "skip" December and have it list "null" for December dates since the team won't ever run a report for December since it's the last month of the year.

For example, I am using this to indicate to Operations to run a report for the first day of the calendar year, through the last date of the prior month. I have three columns.

1. First day of the calendar year - date
2. Last Day of Prior Month
3. Year to Prior End of Month - formula that concatenates columns to tell the Operator the dates they need to run. (="Run Year to Prior End of Month Report For: " + Date2 + " - " + [Last Day of Prior Month]@row)

If the operator is using today, it should read "Run Year to Prior End of Month For: 01/01/2023 - null" and it's reading "Run Year to Prior End of Month For: 01/01/2023 - 12/31/2022" which is causing a lot of confusion as you can imagine.

Paul Newcome

@Allison Horn Ah. Understood. In that case we would just use an IF statement.

=IF(MONTH(TODAY()) = 1, "null", DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)

Allison Horn

That worked great, thank you!

Paul Newcome

Happy to help. 👍️

JIM_AFO

@Paul Newcome I have a Month column which is a drop down selection consisting of every month. January-December. Is there a way to return in the Due date column a value based off the Month selected? Assume some sort of "IF" formula.

For example - IF Month@row = "January" Then Due date column would return 1/31/23 or the last day of each corresponding month. Also, if i needed my due date to be 10 days after the previous Month end so 1/31/23 + 10 days how would that formula be built?

Paul Newcome

@JIM_AFO

You would need to start with a nested IF statement to output the month number based on the month text.

IF(Month@row = "January", 1, IF(Month@row = "February", 2, ...............................)))))))))))

Then you would nest this in the month portion of your date function.

=DATE(yyyy, nested_if, dd)

JIM_AFO

Thanks @Paul Newcome. I actually took a bit of a different approach and used a Helper sheet with the Index/Match formula to return the month end date. I've run into another issue though with returning the actual "Due Date" needed.

I need the Due date to be 10 days after the Month End date. The "Days Post Month End" is manually entered text, The Month Column is a drop down, and the Month End Column is a formula with alternate sheet references =INDEX({2023 FOS Formulas Month End}, MATCH(Month@row, {2023 FOS Formulas Month}, 0))

In the Due date column, I was attempting to use this formula

=[Month End]@row + [Days Post Month End]@row but i'm getting the result in the screen grab below...

I need the due date to be 10 working days post the date in the Month End column.

Paul Newcome

It looks like the [Month End] is being treated as a text string. Are both that column and the reference column set as date type columns? If so, how is the reference column being populated?