How to get first day of the month in Excel

February 18, 2022

To get the first day of the month in Excel for a given date, you can use two simple approaches: using the EOMONTH function or using the DAY function.

Get first day of month

Get first day of the month using EOMONTH function

EOMONTH function returns the last day of a month, N months in the future, or the past. In our case, we will need to retrieve the last day of the previous month so that we can later obtain the first day of the month.

In the example shown, the formula in cell C5 is:
=EOMONTH(B5,-1)+1

There are only two arguments for the EOMONTH function:
start_date – the date that represents the start date, in our case, cell B5.
months – the number of months before or after start_date. Since we need to get the end of the prior month, we will use -1.

Using this formula, Excel will return the last day of the previous month (in our case, November 30, 2018) and then add one extra day.

Note: Excel sees dates as numbers, and adding one will move the date one day into the future.

Get first day of the month using DAY function

Another way to get the 1st of the month is by using the DAY function, which returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.

In our example, cell C11 contains a straightforward formula:
=B11-DAY(B11)+1

Since our original date is December 11, 2018, we will subtract 11 days from the date using the DAY function and then add 1. The result will be December 1, 2018.

If you have additional questions on how to retrieve the first day of the month in Excel, please let me know by posting a comment. I reply to all the comments that I receive.

About me
Radu from ExcelExplained

My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.

If you'd like to get in touch, you can contact me on LinkedIn.

Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?

Essential Functions

Popular Articles

Excel courses

I have compiled a list of the best Excel online courses. It covers basic and advanced topics, VBA, and Power BI.
View courses
Excel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created ExcelExplained.com to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.
Copyright © 2017-2022 ExcelExplained.com
>