headermask image

Business Tools Blog

Beginning of the Month Function in Excel

Yesterday Ting Liu, Finance Lead at Zayo Bandwidth, asked if Excel had a function to calculate the first day of the month.  Great question …

A prior post showed how to use the EOMONTH function.  EOMONTH returns the last day of the month.

You can also use EOMONTH, to calculate the first day of the month.

  • =EOMONTH(A1,-1)+1
  • In the formula below =EOMONTH(A1,-1), returns the last day of the prior month = 10/31/08
  • Then “+1″ adds 1 day to 10/31/08 = 11/1/08

Photobucket

Note - if you receive a #NAME? error, make sure your addins are turned on.

If you liked my post, feel free to subscribe to my rss feeds

Money Making Options

Post meta

This entry was written by Sandi Mays and posted on November 13, 2009 at 12:00 am and filed under Excel. Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment(Latest is displayed first) or leave a trackback: Trackback URL.

Share with others

Post Beginning of the Month Function in Excel to digg. Post Beginning of the Month Function in Excel to Reddit add to stumbleupon Google Bookmark myweb bookmark on del.cio.us newsvine Tailrank magnolia Furl co.mments shadows simpy blinklist

4 Comments so far (Add 1 more)

  1. Would it be possible to use EOMONTH formula to calculate the first day of any month minus 180 days? I’m trying to set up a payment calculation that would start payment on the first day of the month following 180 days from any date.

    Thanks!

    1. Pablo on August 13th, 2009 at 8:10 am

  2. You are a champion - thanks

    2. Simon on July 20th, 2009 at 7:09 pm

  3. Thanks John! These formulas are still a safe bet. I still get calls asking about the #NAME? error.

    3. Sandi Mays on November 21st, 2008 at 4:20 pm

  4. Back in the day (Excel 97), I could never count on other folks having the Analysis Toolpak installed, so I have always used DATE.

    End of month:
    =DATE(YEAR(A1),MONTH(A1)+1,0)

    Beginning of month:
    =DATE(YEAR(A1),MONTH(A1),1)

    NOwadays, people no longer worry about disk space, so they install everything. And of course, Excel 2007 includes the ATP functions as built-in worksheet functions.

    4. Jon Peltier on November 21st, 2008 at 4:31 am

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*