Last Day of Month in Excel

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!

26 thoughts on “Last Day of Month in Excel

  1. Well done old chap.

    Just love the web. You done this in 2004 and I needed it now.

    Thanks again.

  2. 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.

  3. 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.

  4. I hope you still read this thread, because then I can express my eternal gratitude.
    Timestamp mid-2010!

  5. 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.

  6. 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)

  7. 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!.

  8. 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! :)

  9. 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
    .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!

Leave a Reply

Your email address will not be published. Required fields are marked *