Long Time Intervals and Special Dates

It's easy to obtain elapsed time over long intervals by subtracting date values. To determine precise dates and intervals, you can creatively use the component pieces. For example, given the date, what is the first day of the month? This is easy because the first day of every month is day 1. Use the functions Year and Month to extract the year and month, and then reassemble the date using DateSerial and set the day to 1.

 d = Now Print DateSerial(Year(d), Month(d), 1) '08/01/2003 

To find the last day of a month, find the first day of the next month and then subtract 1 from the number. If the current month is December, set the month to January and increment the year by 1.

 d = Now nYear = Year(d)        'Current year nMonth = Month(d) + 1  'Next month, unless it was December. If nMonth > 12 Then    'If it is December then nMonth is now 13   nMonth = 1           'Roll the month back to 1   nYear = nYear + 1    'but increment the year End If Print CDate(DateSerial(nYear, nMonth, 1)-1) '08/31/2003 

It's easy to find the first day of the year for any given date; it's always January 1 of that year. Use the Year function to obtain the current year and then set the day and month each equal to 1. Finding the last day of the year for any given date is only marginally more difficult. First, find the first day of the next year by incrementing the year by 1 and setting the month and day equal to 1. Subtracting 1 from the first day of next year provides the last day of this year.

 d = Now Print DateSerial(Year(d), 1, 1)             '01/01/2003 Print CDate(DateSerial(Year(d)+1, 1, 1)-1)  '12/31/2003 

Use the WeekDay function to find the first and last days of a week. Subtract the day of the week and add 1 to take the date to Sunday at the beginning of the current week.

 d = Date Print Cdate(CDbl(d) - WeekDay(d) + 1)  '8/10/2003 is a Sunday Print Cdate(CDbl(d) - WeekDay(d) + 7)  '8/16/2003 is a Saturday 

You can use similar date manipulations to solve other date- related problems, such as determining the work week, how many days until your anniversary, or the age of a person in years , months, and days.