How to Get the Month’s First Day in Excel (Easy Formulas)

You might occasionally need to determine the first day of the month based on a specific date.

You may quickly use a workaround to discover the first day of a particular month when there is no specific feature to do so.

I’ll demonstrate a pretty straightforward formula in this Excel lesson that you can use to determine the first day of a month in Excel based on a given date.

Then let’s get going!

This instruction explains: 1. Using EOMONTH, find the first day of the month. 2. Using the DATE/DAY method, get the first day of the month 3. Get every month's first Monday

1. Using EOMONTH, find the first day of the month.

The EOMONTH Function provides the month’s last day for a specified date (hence called the end-of-month function).

However, we prefer the first day of the month rather than the last.

You must modify your EOMONTH formula to obtain the beginning of the month.

Let’s say you want to determine the first day of the month for each of the dates in column A for a data collection like the one below.

 

Keep in mind that these dates are in the format MM/DD/YYYY (the date format followed in the US). Depending on the regional date settings in your system, they can appear differently.

The formula to achieve it is as follows:

=EOMONTH(A2,-1)+1

The EOMONTH formula above employs two justifications:

Start Date:

The date from which Excel calculates the start day of the month using the month value.

The Current Month:

The number of months either before or after the commencement date. When I use EOMONTH, it will either give me the final day of the current month, if I use 0, or the last day of the following month, if I use 1.

The EOMONTH formula provides me with the final day of the previous month because I used -1 as the second input (based on the date in cell A2).

In addition, I may simply add 1 to the EOMONTH result as I want the first day of the month (as of the supplied date).

If you want the 10th day or any other day of the month, you can also apply the same reasoning. Simply substitute that value for 1 in the formula.

It should be noted that only Excel 2007 and later versions support the EOMONTH function.

2. Using the DATE/DAY method, get the first day of the month

Excel treats dates as integers, making it simple for us to utilize them in computations (such as addition and subtraction).

Now, all you have to do to get the first day of the month from a given date is utilize the date’s day value and reset it to 1.

For instance, if I have the date as October 15, 2020, I must deduct 14 days from this date to obtain the first day off this month.

Let’s now examine how to convert this reasoning into an Excel formula.

Let’s say I want to find the first day of the month for each of the dates in the data set below where the dates are in column A.

The equation to accomplish it is given below:

=A2-DAY(A2)+1

The date in cell A2 (which is kept as a serial number in the back and Excel) is used in the calculation above, and the daily value of the same date is subtracted (which is given by the DAY formula).

Since we want the first day of the month to start on the given date, we add 1 to the formula above, which gives us the final day of the preceding month.

3. Get every month’s first Monday

Getting the first day of the month is rather simple, but getting the first Monday is probably more difficult (or any other first weekday of the month).

While we are aware that this would occur towards the beginning of the month, we must first determine the day of the month’s first day to determine the first Monday.

For the sake of this lesson, I’ll take Monday as the start of the workweek (you can use the method shown here to find out the first occurrence of any day of the week)

Let’s use September 2020 as an example, where the first Monday is on September 8 and the first day of the month is a Tuesday.

The equation that will provide me with the first Monday in September 2020 is listed below.

=(EOMONTH(DATE(2020,7,1),-1)+1)+(MOD(8-WEEKDAY(EOMONTH(DATE(2020,7,1),-1)+1,2),7))

Please take note that I have used the DATE function to hardcode the date. You may also utilise a cell refeutilizeontaining a date in this situation. Therefore, you may utilize the cell reference (such as A2) that contains the date instead of DATE(2020,7,1).

Let me now explain how this formula functions.

The EOMONTH formula, which makes up the first part of the equation, tells us what day of the month it is on the specified date.

The WEEKDAY function is used in the second portion of the formula to examine this first day of the month. The whole MOD algorithm yields 0 if Monday has already passed, leaving us with the first day alone.

The MOD formula would provide us with the total number of days that we would need to add to this date to obtain the first Monday of the month, however, if the first day of the month is not a Monday.

For instance, the first day of the month falls on a Tuesday in September 2020. The MOD function returns 6 in this instance, which, when added to the EOMONTH result, yields September 7, 2020, as the first Monday of the month.

You can use the same technique to locate any day; in this example, I’ve shown you how to get the first Monday of any month.

For instance, you may modify the method as follows to get the first Saturday of any month:

=(EOMONTH(DATE(2020,9,1),-1)+1)+(MOD(8-WEEKDAY(EOMONTH(DATE(2020,9,1),-1)+1,16),7))

The second justification of the WEEKDAY formula is the only modification I have made.

To determine the first day of the month or the first Monday (or any other weekday) of the month, you may use the Excel formula shown above.

I sincerely hope this instruction was helpful.