How to Get the First Day of the Month in Excel

This guide will discuss how to get the first day of the month in Excel.

Excel contains many tools and functions we can use to perform several tasks. Furthermore, it does not end with calculations. But, we can also use several functions to manipulate dates. 

And data are extremely crucial when dealing with financial tasks. When dealing with finance, we may need to get different kinds of dates, such as the first day or the beginning of a month, based on a given date. 

But, Excel does not have a specific function we can use that solely and directly calculates the first day of the month. Fortunately, Excel has other functions we can use and manipulate easily to get the first day of the month. 

Let’s take a sample scenario wherein we must get the first day of the month in Excel.

Suppose you are creating a financial report for the year 2021. And you need to obtain the first day of the month based on the given dates to calculate the sales. When dealing with a large data set, it can be difficult and a waste of time to manually input the first day of the month.

So you opted to use the DAY function to get the first day of the month depending on the given dates. 

Great! Before we move on, let’s first learn how to write the DAY function in Excel.

The Anatomy of the DAY Function

The syntax or the way we write the DAY function is as follows:

=DAY(serial_number)

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we begin any function in Excel.
  • DAY() refers to our DAY function. And this function is used to return the day of the month. Furthermore, this number must be from 1 to 31, which is based on the number of days in a month.
  • serial_number is a required and the only argument for the DAY function. And this refers to the number in the date-time code used by Excel. For example, one of the codes is dd-mm-yyyy.

Next, let’s discuss how to write the EOMONTH function in Excel.

The Anatomy of the EOMONTH Function

The syntax or the way we write the EOMONTH function is as follows:

=EOMONTH(start_date, months)

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we can activate any function in Excel.
  • EOMONTH() is our EOMONTH function. And this function is used to return the serial number of the last day of the month before or after a specific number of months. 
  • start_date is a required argument. And this refers to a serial date number that will represent the start date.
  • months is another required argument. So this refers to the number of months before or after the start_date argument. 

Amazing! Now we can dive in and learn about a real example of getting the first day of the month in Excel.

A Real Example of Getting the First Day of the Month in Excel

Let’s say we have a data set containing specific dates. And we need to get the first day of the month of each given date. So our initial data set would look like this:Initial data set

 

Firstly, we can utilize the DAY function to get the first day of the month of the given dates. So we will be creating a formula using the DAY function. In this case, the DAY function will return the day value of the date.

Then, we only need to subtract the days from the returned value. After, we will add 1 to get the first day of the month. For example, the DAY function formula to get the first day of the month would be =date-DAY(date)+1.

Secondly, we can also use the EOMONTH function to obtain the first day or the beginning of the month. Similarly, this function will take a given date and return the last date of the month. Afterward, we can add 1 into the function to get the first day of the month.

So the EOMONTH formula to get the first day of the month would be =EOMONTH(date,-1)+1. 

Additionally, we can also get the first day of the month based on a given month’s name. To do this, we can use the DATEVALUE function. So this function will convert a date in the text form to a number that will represent the date in the Excel date-time code.

So we can use this function to create a formula that will get the first day of the month. Basically, the formula will create a date by combining the day, month, and year. Then, it will convert it into an actual date. 

Then, our final data set after using the different methods would look like this:

Final data set

 

You can make your own copy of the spreadsheet above using the link attached below. 

Finally, we can discuss the process of how to get the first day of the month in Excel using the different functions.

How to Get the First Day of the Month in Excel

In this section, we will explain how to get the first day of the month in Excel using the DAY function, the EOMONTH function, and the DATEVALUE function. To learn how to use these functions, follow the steps below.

1. Firstly, we will create a new column in the data set to input the results. Then, we will start the function by typing an equal sign. After, we can simply type in the formula “=B3-DAY(B3)+1”. Lastly, press the Enter key to return the result.

Get the First Day of the Month in Excel

 

2. Secondly, we will drag the fill handle down to apply the formula to the rest of the cells.

Copying the formula

 

3. And tada! We have successfully used the DAY function to get the first day of the month.

Fina output

 

4. Next, we will use the EOMONTH function to obtain the first day of the month. Similarly, we will start the function with an equal sign. Next, we will input the formula “=EOMONTH(B3,-1)+1”. Then, press the Enter key to return the result.

Get the First Day of the Month in Excel

 

5. Afterward, we will drag down the fill handle to copy the formula and apply it to the other cells.

Get the First Day of the Month in Excel

 

6. And tada! We have successfully used the EOMONTH function.

Get the First Day of the Month in Excel

 

7. Additionally, let’s try using the DATEVALUE function to get the first day of the month based only on the month name and year. So simply type in the formula “=DATEVALUE(“1″&”/”&E3&”/”&E5)”. Then, press the Enter key to return the result.

Get the First Day of the Month in Excel

 

8. And tada! We obtained the first day of the month using the DATEVALUE function.

Get the First Day of the Month in Excel

 

And that’s pretty much it! We have discussed how to get the first day of the month. Now you can choose any of the methods discussed and apply them to your work when needed. 

Are you interested in learning more about what Excel can do? You can now use the EOMONTH function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like