This Excel tutorial explains how to find first date of month, last date of month using Excel worksheet Function and Excel VBA Function.
You may also want to read:
Excel Use Date in IF Condition
Excel first date of month
To find first date of month in Excel, first we have to understand DATE Function, you can use it to combine the year, month and day to a date.
Syntax of Date Function
DATE( year, month, day )
Example
| Formula | Result | Explanation |
| =Date(2012,1,1) | 1/1/2012 | Combine all three arguments to date 2012 Jan 1st |
| =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())) | 13/3/2016 | Year Function returns year of the date, Month function returns month of a date, Day function returns day of a date.
Assume today is March 13th, use today’s year, month, day as the arguments to return today’s date |
To get the first date of specific month, say this month, use today’s month and year, and then use 1 for day argument
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
Excel last date of month
There are several ways to find the last date of month, I am introducing two ways that I like most.
Unlike first date of month, we cannot directly input “31” in the day argument because every month has different number of days. Instead we can find the first day of next month and then minus 1 day. Excel is very clever that when you add 1 month to December, the year also adds 1, so this trick also works for year end. The formula below returns the last date of current month.
=Date(Year(Today()),Month(Today())+1,1)-1
We can also simply this formula using 0 in the day argument instead of using -1. 0 returns the last date of previous month.
=Date(Year(Today()),Month(Today())+1,0)
Excel VBA first date and last date of month
In VBA, the relevant Function name is different but the logic and arguments are the same.
| Excel Worksheet Function | VBA Function |
| Date | DateSerial |
| Today() | Date() |
| Year | Year |
| Month | Month |
| Day | Day |
Example
| Formula | Result | Explanation |
| Dateserial(2012,1,1) | 1/1/2012 | Combine all three arguments to date 2012 Jan 1st |
| DateSerial(Year(Date), Month(Date), Day(Date)) | 13/3/2016 | Assume today is March 13th, use today’s year, month, day as the arguments to return today’s date. |
| DateSerial(Year(Date), Month(Date), 1) | 1/3/2016 | First date of current month |
| DateSerial(Year(Date), Month(Date)+1, 0) | 31/3/2016 | Last date of current month |