My next Excel Top Tip is how to calculate the last day of a month. I thought there was a function to do this as part of a whole suite of date manipulation functions, but I seem to have imagined that, as I can’t find it now. I was dreading having to do lots of nasty date arithmetic, but then I discovered that the zeroth day of a month is treated as the last day of the previous month! So the last day of the month for the date that’s in cell B3 is simply:
=DATE(YEAR(B3), MONTH(B3)+1, 0)
It even works across year boundaries, so DATE(2004, 13, 0) really does give December 31st 2004!
Well done old chap.
Just love the web. You done this in 2004 and I needed it now.
Thanks again.
Great help!! Thank you.
Thanks!!!
Actually, Excel DOES offer the EOMONTH function to do get the last day of the month, but it’s not part of the main set of functions. You have to activate the Analysis Toolpack (under Tools->Add-ins) to get it.
Of course, if you’re sharing your spreadsheet with other users, THEY have to make sure they have it enabled, too. Otherwise, all those nice dates you see on your screen turn into #NAME? errors on theirs. I’ve been having to deal with this at my office recently (today, in fact). I think I might just switch to your solution instead.
much useful.
Thanks so much.
Valdemar
That helped me impress my peers – three cheers for you !! :)
PaNK CoOL
Plus one to thank you.
excellent information. Many more thanks for this.
Freaking brilliant.
I used this for a sales calculation to mark revenue as ‘in month’ if the projected close was less than the last day of the month.
How do you calculate the last business day of the month????
I think you just saved me an entire weekend!
thanks a lot :-)
You are a rockstar! Thanks!
@Louis Lodder
HERE HERE. I us3ed it again in 2009!
Tip still works a treat in 2010, over 5 years since the original post! Top tip!
I hope you still read this thread, because then I can express my eternal gratitude.
Timestamp mid-2010!
Thank you
I have been looking for this for a long time and yours is the first solution that works and is simple to show to others.
Thanks friend! And thanks to Andrew Thompson too!
tank you, was only struggeling on the formula in excel 2007, it won’t go as posted =DATE(YEAR(B3), MONTH(B3)+1, 0)
but had to be:
=DATE(YEAR(B3); MONTH(B3)+1; 0)
same as:
=eomonth(b3,0) had to be =eomonth(b3;0)
Excellent solution. I wanted something that does not require downloading add-on pack since there is no quarantee that the end-users will have this, or the know-how to download the pack.
Just noticed this solution was posted in 2004 and yet folks are coming here to find this exact solution. VERY COOL!.
Many thanks, this formula really helped me.
Legendary – thank you!
Many thanks for your wonderfully simple solution.Saved my day :)
Woo-hoo! Ask and you shall receive! Last day of previous month is a key field in a massive monthly report – wrong date makes report completely wrong. … I no longer have to worry that I will miss updating the date in my hot pink date field that turns florescent green after 31+ days! (Usually catches my attention, but now I can turn off the obscene color combo! ;).
Thank you! :)
I’d like to use this (via the MS Excel Application object and Worksheet Functions) in an MS Access macro
here is what I came up with thanks to your tip:
Sub getLastDayOfMonth()
Dim sFullDate As String
Dim sDate As Date
Dim sMonth As String
Dim objExcel As Object
Set objExcel = VBA.CreateObject(“Excel.Application”) ‘
sDate = VBA.InputBox(“Enter the date”)
With objExcel
.Workbooks.Add
.Workbooks(1).Activate
.Range(“A1”).Value = sDate
.Range(“A2”).Formula = “=DATE(YEAR(A1),1+MONTH(A1),0)”
sMonth = .WorksheetFunction.Text(sDate, “mmmm”)
sFullDate = .WorksheetFunction.Text(objExcel.WorksheetFunction.WeekDay(sDate), “dddd”) ‘
sFullDate = sFullDate & ” ” & Day(.Range(“A2″).Value) & ” ” & sMonth & “, ” & Year(.Range(“A2”).Value)
End With
MsgBox “The last day of the month for ” & sDate & ” is ” & sFullDate
Debug.Print sFullDate
objExcel.Workbooks(1).Close False
Set objExcel = Nothing
End Sub
it works – just wish there was something so useful natively in MS Access!
and guess what, after doing all that to re-use your funtcion in MS Access, I came accross the MS Access Native function DateSerial!
http://www.techonthenet.com/access/functions/date/dateserial.php
problem solved!