47. Excel Tips - Calculate the LAST Day of the month
This is a follow-on from tip on how to calculate the 1st day of the month in Excel
Suppose we want to calculate the last day of the month... can we use the same function ?
YES, but we need to be a bit more cunning this time.
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1
- will return the last day of the month.
- it is basically saying take the 1st day of NEXT month, and subtract 1 day.
Download a spreadsheet to practice calculating the last day of the current month in Excel
It Even works for leap years.
Training video on calculating the last day of the month in Excel:
»
- Nick's blog
- Login or register to post comments
- 14236 reads
Calculate the last day of the month
Here's a shorter formula:
=EDATE(TODAY(),1)-DAY(TODAY())
Best Formula
=Eomonth(date,0)