How to Get the First Day of the Month in Excel

In many industries, such as finance or accounting, being able to work with dates is an important skill. In particular, being able to find the first day of the month is a common task. In this tutorial, you’ll learn how to get the first day of the month in Excel. This allows you to, for example, identify how far away a given date is from the beginning of the month.

There are a number of easy ways in which you can get the first day of the month. By the end of this tutorial, you’ll have learned how to do the following:

• Use the DAY() and EOMONTH() functions to get the first day of the month
• Use the TODAY() function to get the first day of the current month

How to Get First Day of the Month in Excel Using DAY()

The Excel DAY() function allows you to easily calculate the day of the month in a provided date. For example, if you pass June 23, 2023 into the DAY() function, you’ll return 23. Because of this, we can easily calculate the first day of a month of a given date by using the DAY() function.

Let’s take a look at the formula we’d use to accomplish this, assuming that our date is stored in cell A2:

=A2 - DAY(A2) + 1

In order to better illustrate this, let’s take a look at a step-by-step example, walking us through how to calculate the first day of the month for each value in a column.

See – that wasn’t too bad! In the following section, you’ll learn how to use the EOMONTH() function to find the first day of the month.

How to Get the First Day of the Month in Excel USING EOMONTH()

Similar to the example above, we can use another function, EOMONTH(), to calculate the first date of a month of a given date. The EOMONTH() function is used to return the last date of the month of a date that’s passed into the function. What’s more is that the function allows you to pass in a value that represents how many months you want to move up or down.

This may seem a bit complicated at first, so let’s take a look at an example. Say we pass in the date of June 23, 2023 and a month count of -1. In this case, the function will look like this:

=EOMONTH(A2, -1)

In this case, we’re assuming that the date is stored in cell A2. From there, Excel calculates the end of the month and moves back one month. This, then, returns May 31, 2023. However, we want the first day of the date’s month! In order to do this, we simply need to just add 1 to the value:

=EOMONTH(A2, -1)+1

This would now return June 1, 2023.

Let’s dive into a hands-on example how to use this function to get the first day of a month in Excel.

Step 1: Load a Dataset

In the image below, we’re using the same dataset that we used before. The dates range from June 2023 through December 2023. We want to get the first date of the month for each of these dates in column B.

Step 2: Use the EOMONTH() Function to Get the First Date

While the EOMONTH() function is used to calculate the last date of a month, we can modify the formula to return the first day of the month. We pass in a month modifier of -1 to give us the last date of the preceding month. From there, we add 1 to get the first date of the month.

Step 3: Hit the Return Key to Get the First Date of the Month

When you hit the Return key, the function is evaluated. In this case, the function returns June 1, 2023.

Step 4: Drag the Fill Handle All the Way Down

By selecting and dragging the fill handle all the way down, the formula is copied for all the cells. This quickly calculates the first day of the month for each of the provided dates.

In the following section, you’ll learn how to get the first day of the month for the current date.

How to Get the First Day of the Current Month in Excel

In this final section, you’ll learn how to use Excel to get the first day of the current month. This works in the same way as previous examples, but we’ll pass in the TODAY() function as the date. As the name implies, the TODAY() function returns today’s date. Let’s see how we can calculate the first day of the current month.

In the cell, enter in =EOMONTH(TODAY(), -1)+1. This calculates the first date of the current month. Take a look at the image below to see what this looks like:

When you hit the Return key, the function evaluates and returns the first date of the current month. In the example of the image below, this returns November 1, 2022.

Using the steps above, you can see how easy it was to get the first day of the current month in Excel!

Conclusion

In this tutorial, you learned how to use Excel to get the first day of the month of the provided date. This can be helpful to calculate groupings of values or better understand the data that you’re working with. You first learned how to use the DAY() function to calculate the first date of a given month. Then, you learned an alterative to calculate the first date of the month using the EOMONTH() function. Finally, you learned how to find the first date of the current month by passing the TODAY() function into one of the methods.

Additional Resources

To learn more about related topics, check out the tutorials below:

• How to Get the First Day of the Week in Excel
• How to Get the First Day of the Quarter in Excel
• How to Get the First Day of the Year in Excel
• Excel EOMONTH() Function: Official Documentation