Excel Formula: Get First Day or Last Day of Month from A Date
In this tutorial, it provides formulas to return the first day or last day of month from the given date in Excel.
If you want to follow along with this tutorial, please download the example spreadsheet.
Get first day of month from a date
Generic formula:
| date-DAY(date)+1 |
Syntaxt and Arguments
| Date: the date that is used to find the first day of month. |
Return Value
It returns the first day of the current month in date format.
How this formula works
Use below formula to get the first day of month from the date in cell B3:
| =B3-DAY(B3)+1 |
Press Enter key to get the result.
Explanation
DAY function: returns the day from the given date as number.
Since the date is stored as number in Excel, 2/12/2019 is stored as 43508 in Excel.
=B3-DAY(B3)+1
=43508-DAY(2/12/2019)+1
=43508-12+1
=43496+1
=43497 which is shown as date 2/1/2019
Get last day of month from a date
Generic Formula
| EOMONTH(date,0) |
Syntax and Arguments
| Date: the date that is used to find the last day of month. |
Return Value
It returns the last day of current month in number format.
How the formula works
Use below formula to get the last day of month from the date in cell B3:
| =EOMONTH(B3,0) |
Press Enter key to get the result. The result displays as a 5-digit number.
Keep the formula result selected, and press Ctrl + 1 to display the Format Cells dialog, then under Number tab, click Date from the Category list, choose the date type you want to display in the Type section.
Click OK, the result is shown as date.
Explanation
EOMONTH function: gets the last day of month n months in future or past.
Relative Formulas
- Get first day or last day by month text name
Let’s say there is a table containing the year and the month text name for you to get the first day of the month, which formula can you use in Excel? - Count days until expiration date
To count the days between today and each expiration date in Excel by formula - Display current date or time
This tutorial provides formulas to show the current date or time in Excel. - Create weekly date range
In this tutorial, it provides a formula to quickly get the weekly date range in Excel.
Relative Functions
- Excel WEEKNUM Function
Returns the week number of the given date in a year - Excel WORKDAY Function
Adds workdays to the given start date and returns a workday - Excel MONTH Function
The MONTH is used to get the month as integer number (1 to 12) from date - Excel DAY Function
DAY function gets the day as a number (1 to 31) from a date - Excel NOW Function
Get the current time and date
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
