< Go Back

Get first day of month

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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar