The Excel DAY function returns the day of the month as a number between 1 to 31 when it is provided with a date. The DAY function can also be used to get the first day of the month in Excel. For a given date, to get the first day of the month you can use a simple formula based on the DAY function.

**Formula**

**=date-DAY(date)+1**

**Explanation**

The formula incorporating Excel DAY function to get the first day of month uses the following argument:

**Date** (required): It is the date of a particular month for which the first day is to be found.

This works as follows:

- The DAY function returns the day of the month.
**The return type value is a number between 1 and 31**. - It is then subtracted with the date as dates are just serial numbers in Excel.
- Then, 1 is added to the date value in order to get the first day of a month.

**Example 1**

In this example, we have several random dates. To get the first day of the month, the following formula is used using Excel DAY function;

`=D5-DAY(D5)+1`

This works as follows:

- DAY(D5) returns the day of the month which is number 19.
- 19 is then subtracted from the date as dates are just serial numbers in Excel.
- Then, 1 is added to the date value which gives the first day of a month that is 01-02-2018.

* Figure 1. Example of DAY function to get the first day of month 1*

**Example 2**

In this example also, we have several random dates. To get the first day of a month, the following formula is used using Excel DAY function;

`=D8-DAY(D8)+1`

This works as follows:

- DAY(D8) returns the day of the month which is number 04.
- 04 is then subtracted from the date as dates are just serial numbers in Excel.
- Then, 1 is added to the date value which gives the first day of the month that is 01-12-2018.

*Figure 2. Example of DAY function to get the first day of month 2*

**Notes**

As an alternative, the EOMONTH function which returns the last day of the month can also be used to get the first day of a month. You can get the first day of the current month with a formula like this;

`=EOMONTH(date,-1) + 1`

This formula rolls back a date to the last of the previous month and then adds 1. The result is the first day of the “current month”.

**Example 3**

In this example, we have several random dates. To get the first day of a month, the following formula is used using Excel EOMONTH function;

`=EOMONTH(D6,-1)+1`

This works as follows:

- EOMONTH(D6,-1) returns the value of the
**date to the last of the previous month which in this case is 31-01-2018**. - Then, 1 is added to the date as dates are serial numbers in Excel and helping us get the first day of the month.

*Figure 3. Example of EOMONTH function to get the first day of a month *

## Leave a Comment