Access first date of month and last date of month

This Access tutorial explains how to find first date of month, last date of month using Access Expression and VBA Function.

You may also want to read:

Excel first date of month and last date of month

Access first date of month

To find first date of month in Access, first we have to understand DateSerial, you can use it to combine the year, month and day to a date.

Syntax of Date Function

DateSerial( year, month, 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 Year Function returnsĀ  year of the date, Month function returns month of a date, Day function returns day of a date.

Date() Function is to return today’s 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

=DateSerial(YEAR(Date()),MONTH(Date()),1)

Access 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. Access 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.

DateSerial(Year(Date()),Month(Date())+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.

DateSerial(Year(Date()), Month(Date()) + 1, 0)

Access VBA first date and last date of month

In VBA, the relevant Function names are same as the Expression Function names, except that Date() Function in Expression becomes Date (without brackets).

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

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *