# 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:

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:

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.

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

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

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.

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

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

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.

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

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.

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.

## Fix Excel When Rows and Columns are Both Numbers – Sheetaki

This guide will explain how you can fix Excel spreadsheets where the rows and columns are both displayed…

## How to Use SetFocus VBA in Excel

The SetFocus method in Excel VBA is useful when you need to program the user’s cursor to focus…

## How to Forecast Cash Flow in Excel

This guide will explain how to forecast cash flow in Excel. A forecast cash flow is a useful…

## How to Find Confidence Interval for Population Mean in Excel

This guide will help you understand how to calculate the confidence interval of a population mean in Excel.…