How to: Get the First Day of the Month for any input date

Below are 3 different methods to calculate the 1st day of the month from any given date.

When working with each of the methods below we will assume our date is in cell A1 for simplicity.

Method 1

Using DATE, YEAR, MONTH functions:

=DATE(YEAR(A1), MONTH(A1), 1)

How it works

The DATE function takes 3 arguments to form a date: a year number, and month number and a day number.

=DATE(year_num, month_num, day_num)

So DATE function will take a set of integer values for year, month and day and create a date out of it

=DATE(2020,12,25)

will give the follow output, as a date data type.

12/25/2020

NOTE: This is in US date format, your regional settings will determine how date are formatted for you.

YEAR and MONTH and DAY functions

To get the year number from our starting date we can use the YEAR function

=YEAR(date)

YEAR takes a date and returns a number representing the year part of the date.

Similarly, for MONTH, which takes a date and returns the month (of the year) number.

=MONTH(date)

And although we will not use the DAY function here, for completeness this function also takes a date and returns the day (of the month) number,

=DAY(date)

So using YEAR, MONTH and DAY functions with the date December 25, 2020 as the input:

YEAR would return the number 2020, and
MONTH would return the number 12
DAY would return the number 25

NOTE: The the outputs of YEAR, MONTH and DAY functions are no longer dates, just integer numbers.

Simply stating the day value is the number 1 we can rebuild the date with the DATE function. We will use the year and month numbers we calculated using YEAR and MONTH functions, and use the number 1 as our day_num to return the 1st day of the month – December 1, 2020.

=DATE(2020, 12, 1)

Recap

To get the year number from our date we use the YEAR function,
To get the month number from our date we use the MONTH function
To get the first day of the month we simply use the number 1
We then rebuilt the new date with the DATE function, supplying the year & month numbers calculated from our original date, and the number 1 for the day_num value.

Method 2

Using the EOMONTH function:

With the knowledge of how the DATE and EOMONTH function can return the same values in some situations, we can use the EOMONTH function instead to give the same result.

=EOMONTH(A1, -1) +1

How it works

EOMONTH is short for ‘End Of Month’, which accepts as an input: a date, and a number (representing a number of +/- months relative to the input date) and outputs a date of the end of the month of the input date.

=EOMONTH(date, number_of_months)

The first of the month formula, accepts the date from cell A1.
The second argument of -1 means EOMONTH will return the date of the end of the previous month to the input date,
Finally we add ‘one day’, +1, outside the EOMONTH function, remembering that all dates in Excel are based on number of days since 1/1/1900.

Adding one day to the ‘end of the previous month’s date’ given by our EOMONTH function, gives us the first day of the current month for the date supplied.

Example

For December 25, 2020, with a zero relative month value:

=EOMONTH(date,0)

would return (in US date format), the end of that same month.

12/31/2020

Since every date in Excel is mapped to a Microsoft number value representing the number of days since January 1, 1900, adding 1 will increment the date by 1 day.

So in our EOMONTH solution, we are

returning the date of the end of the preceding month of the input date, by using the month_number argument to be -1
then adding a day with +1, to return the first day of the following month (which is the first day of the month of the date we inputted originally).

Method 3

Using the DAY function:

=A1 -DAY(A1) +1

How it works

The DAY function accepts a date and returns the day number of the month of that date.

=DAY(date)

So,

-DAY(A1)

is the negative day number of the date in question, e.g. day 15 becomes -15.

As previously stated ‘every date in Excel is mapped to a Microsoft number value’ representing the number of days since January 1, 1900′, therefore when we subtract from the date the number of days in the month, we get to the zero^th day of this month, or in Excel’s date numbering: its the equivalent of the last day of the preceding month.

Finally when we add “+1” to the ‘last day of the previous month’ we end up with the 1st day of the following month… or in our example the 1st day of the month and year of our original date.

BEWARE: Method 3 is of limited use however, as it cannot easily be updated to find the first day of the following or previous month, unlike the other functions above.