Calculate and display specific date values

Because a Date value is stored as a double-precision number, you may receive incorrect formatting results when you try to manipulate Date values in an expression. This topic illustrates how to create expressions and custom functions for displaying specific dates and calculating time intervals.

Current month

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

Next month

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

Last day of the current month

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

Last day of the next month

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

First day of the previous month

DateSerial(Year(Date()), Month(Date())-1,1)

Last day of the previous month

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

First day of the current quarter

DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 1, 1)

Last day of the current quarter

DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 4, 0)

First day of the current week (assuming Sunday = day 1)

Date() - WeekDay(Date()) + 1

Last day of the current week

Date() - WeekDay(Date()) + 7

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.