Excel first date of month and last date of month

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

 

Outbound References

https://support.office.microsoft.com/en-gb/article/DATE-function-9b619152-52d4-48f0-b1ab-a32dee59b6e4?CorrelationId=2fb5edef-b55a-4aa8-b5c3-deb71eb98471&ui=en-US&rs=en-GB&ad=GB

Leave a Reply

Your email address will not be published.