ExcelExplained

Get first day of the month

To get the first day of the month in Excel for a given date, you can use two simple approaches: using EOMONTH function or using 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.

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

There are only two arguments for 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. Adding 1 will move the date one day into the future.

Get first day of the month using DAY function

Another way to get the first day of the month is by using 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 very simple formula:
=B11-DAY(B11)+1

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

If you have additional questions please let me know by posting a comment.

Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Beautiful Excel line chart

Improve your Excel skills

Learn how to work with functions, create complex formulas and design beautiful charts. All free.

You have Successfully Subscribed!