Summary

To get the first day of the month for a given date, you can use a simple formula based on the DAY function. In the example shown, the formula in cell C5 is:

=B5-DAY(B5)+1

Generic formula

=date-DAY(date)+1

Explanation 

The DAY function returns the day value for a date. In the example show, the day value for the date in B5 (January 11, 2016) is 11. Because dates are just serial numbers in Excel, we can subtract 11, then add 1 to get the date value for January 1, 2016.

Alternative with EOMONTH

The EOMONTH function returns the last day of in the month of a given date. This means you can get the first day of the current month with a formula like this:

=EOMONTH(A1,-1)+1

This formula "rolls back" a date in A1 to the last of the previous month, then adds 1. The result is the first day of the "current" month (i.e. first day of the month given by the date in A1).

Related formulas

Excel formula: Get last day of month

Get last day of month

The 2nd argument ( months ) of the EOMONTH function allows you to get the last day of the month in future or past months. When you use zero for months, EOMONTH will return the last day of the month in the same month. To get the last day of the prior month, use: =EOMONTH(date,-1) To get the last day...
Excel formula: Days in month

Days in month

The DAY function returns the day component of a date. The EOMONTH function returns the last day of the month for a given date. So, in this formula EOMONTH first returns a date corresponding to the last day of the month, and then DAY returns the date value for that date. By definition, the value...
Excel formula: Get last working day in month

Get last working day in month

Working from the inside out, the EOMONTH function gets the last day of month of any date. To this result, we add 1, which results in the first day of the next month. This date goes into WORKDAY function as the "start date", along with -1 for "days". The WORKDAY function automatically steps back 1...
Excel formula: Dynamic calendar grid

Dynamic calendar grid

Note: This example assumes the start date will be provided as the first of the month. See below for a formula that will dynamically return the first day of the current month. With the layout of grid as shown, the main problem is to calculate the date in the first cell in the calendar (B6). This is...

Related functions

Excel DAY function

DAY Function

The Excel DAY function returns the day of the month as a number between 1 to 31 from a given date. You can use the DAY function to extract a day number from a date into a cell. You can also use the DAY function to extract and feed a day value into another function, like the...
Excel EOMONTH function

EOMONTH Function

The Excel EOMONTH function returns the last day of the month, n months in the past or future. You can use EOMONTH to calculate expiration dates, due dates, and other dates that need to land on the last day of a month. Use a positive value for months to move forward in time, and a negative number...
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions

Training