How to Find First Day of the Month in Excel: Step-by-Step

All Excel functions are useful in many different ways.

You can look up, calculate values, and a whole lot more.

And by more, you can also find the first day of the month in Excel.

There is no direct function for determining the first date of the month based on a specified date. But with a formula, it’s possible in Excel πŸ˜‰

Learn exactly how to find the first day of the month in this Excel tutorial πŸ‘

Excel formula for the first day of the month

You have to find the first day of the given dates in the example below.

Get first day in column B cells

You can use a formula to find the beginning date of the month πŸ₯³

Let’s learn a simple formula to find the start date of a month πŸ“†

  1. Enter an equal sign and select the EOMONTH function.

The EOMONTH function helps to get the last day of a month after adding or deducting a specified number of the month.

=EOMONTH(

Enter an equal sign and select the EOMONTH function
  1. Select the given date as the start date.

In this example, you have to select cell A2.

So, your updated EOMONTH formula is;

=EOMONTH(A2

Select the first argument of the EOMONTH function
  1. Enter minus 1 (-1) as the second argument of the EOMONTH function and close the parenthesis. By entering -1, you can get the last date of the previous month.

So, your updated EOMONTH formula is;

=EOMONTH(A2,-1)

EOMONTH formula to get the last date of the previous month

You have now finished the first part of the formula. So now you have got the last day of the previous month.

  1. The second part is to add a one to the date of the first part. Then you can get the first day of the current month.

After adding 1, the formula bar should show the following formula.

=EOMONTH(A2,-1)+1

You can apply the same formula for other dates as well.

Finding the first day of month using the EOMONTH formula

If the result of the formula is a serial number of the date value, you have to change the cell format to “Date”.

Kasper Langmann, Microsoft Office Specialist

In summary, this formula works like this. First, we find the last day of the previous month of the original date.

Then, we add one day to get the first day of the current month.

Pro Tip!

You can also use the DAY function to get the first day of the month on a given date 🀩

The DAY function returns the day value or day number of a specified date.

In order to get the answer, subtract the outcome of the DAY function from the original date and then add 1.

=A2-DAY(A2)+1

First day of the month using the DAY function

The EOMONTH formula and this formula both perform in the same way.

To determine the beginning day of the current month, first, get the end date of the previous month and then add one.

That’s it – Now what?

Nice work! πŸ‘

Getting the first day of the month might be a simple thing to do and you might think that you don’t need an Excel formula for it.

But now you learned that it can be done in Excel, among other amazing things.

Learning and practicing Excel functions is an excellent investment for you, especially in this digital information age πŸš€

I advise you to start your journey in Excel by learning the most useful and popular ones, such as the IF, SUMIF, and VLOOKUP functions.

How? Enroll in my 30-minute free online course here that will teach you these (and many more) Excel functions in the most fun way πŸ˜€

Other resources

It is helpful to have a basic understanding of date functions in Excel while referring to this article.

You can read our article about All Time & Date Functions article to get more insight.