Total Visual SourceBook

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office 2007 and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!

Separate version for Access/Office 2003, 2002, and 2000.

Separate version for:
Access 97/95


View all FMS products for Microsoft AccessAll Our Microsoft Access Products

SourceBook Info:

Why SourceBook?

 

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

 

 

 

Microsoft Access, Office/VBA, VB6 Modules royalty-free source code libraryMicrosoft Access Modules royalty-free source code libraryMicrosoft Access VBA and VB6 Module Library Free trial of Total Visual SourceBook for Microsoft Access, Office, VB6 and VBA

Module: DateTime in Category General VBA/VB6 : Date and Time from Total Visual SourceBook

Date and time calculations for adding and subtracting dates, day of week or month, quarters, ISO week numbers, date/time parts, translating time to words, etc. using VBA and VB6.

VBA and VB6 provides many useful functions such as DateAdd, DateDiff, DateSerial, etc. for dealing with date and time values.

Procedure List

Procedure Name

Type

Description

(Declarations) Declarations Declarations and private variables for the modDateTime module
AddDays Procedure Add or subtract a number of days to a date.
AddHours Procedure Add or subtract a number of hours to a date/time.
AddWeekdays Procedure Add or subtract a number of weekdays to a date. Weekend dates are not counted in adding/subtracting days.
AddWeeks Procedure Add or subtract a number of weeks to a date.
AddMonths Procedure Add or subtract a number of months to a date.
AddYears Procedure Add or subtract a number of years to a date.
DaysInMonth Procedure Get the number of days in a month.
DiffDatesFormatted_CalculatedByCalendar Procedure Get the number of Years, Months, Days, Hours, Minutes, and Seconds between two dates based on calendar days. Calculations based on calendar days and months. If you use a leap date such as: #2/28/2012 2:01:30 AM#, #3/1/2012 2:01:29 AM# or #2/29/2012 2:01:30 AM#, #3/1/2012 2:01:29 AM#, the output will be the same because this function uses the calendar year for its calculations. Why? Since the end year uses 2/28/2013 as the end of the month for February (it's not a leap year), the result is: (2/29/2012 to 2/28/2013 = 1 Full calendar year) even though only 364 days have passed. Now add 1 day to 2/28/2013 or add 1 day to 2/29/2013 (if it existed) and the result is 3/1/2013 in both cases.
DiffDatesFormatted_CalculatedBySecondsPerYear Procedure Get the number of Years, Months, Days, Hours, Minutes, and Seconds between two dates based on the number of seconds. Calculations are based on the number of seconds per year. That means the number of months, etc., are averaged based on seconds per year. DiffDatesFormatted_CalculatedBySecondsPerYear is designed to show you averaged values based on the type of value you pass it. For instance, if you pass it 38959290 seconds, it will return "1 year, 2 months, 25 days, 2 hours, 1 minute, 30 seconds" based on 60 seconds per minute and 365 days per year. Note that the difference between 1/1/2005 and 3/26/2006 2:01:30 AM is 1 year, 2 months, 25 days, 2 hours, 1 minute, 30 seconds by calendar, and is 38800890 seconds using DateDiff("s", #1/1/2005#, #3/26/2006 2:01:30 AM#). So, a calendar year's seconds are different than the average. If you want to use a more precise calendar year difference, you can use the function DiffDatesFormattedYMDHMS_CalculatedByCalendar.
DiffDays Procedure Get the number of whole days between two dates. The date is rounded down (it takes 24 hours to make a day).
DiffDaysFractional Procedure Get the number of days (including fractions) between two date/time values.
DiffTime Procedure Get the difference in hours, minutes, and seconds between two times (date portions are ignored).
DiffMinutes Procedure Get the number of whole minutes between two date/time values. Minutes are rounded down (must have 60 seconds to count as a minute).
DiffMinutesFractional Procedure Get the number of minutes (including fractions) between two date/time values.
DiffSeconds Procedure Get the number of seconds between two dates/times.
DiffWeekdays Procedure Get the number of weekdays between two dates. The days are rounded down (it takes 24 hours to make a day).
DiffWeeks Procedure Get the number of whole weeks between two dates. The week is rounded down (it takes 7 whole days to make a week).
DiffMonths Procedure Get the number of whole months between two dates. Second date's day must be >= first date's day to be considered a full month.
FirstDayOfWeek Procedure Get the first day (Sunday) of the week of a given date.
FirstDayOfNextWeek Procedure Get the first day (Sunday) of the week following a given date.
FirstDayOfLastWeek Procedure Get the first day (Sunday) of the week prior to a given date.
Get4YearShortDate Procedure Get a date string that displays the full date and 4 digit year based on the user's windows settings.
GetDatePart Procedure Get the specific portion of the date requested.
GetRandomDate Procedure Get a random date between the range specified in the arguments, inclusive.
IsLeapYear Procedure Determine if the year is a leap year using the internal DateSerial function. It does this by seeing if the 29th of February exists. Note that although DateSerial appears to use the correct rule for determining leap years, it is implemented in the VB6 Runtime library and may be subject to change in subsequent versions. For complete reliability, you may want to consider using an alternate algorithm for determining leap years. See the IsLeapYear2 function in this module for details.
IsLeapYear2 Procedure Determine if the specified year is a leap year, using simple comparisons to determine leap years without relying on the DateSerial method. Most Microsoft products use the Gregorian calendar which was created to eliminate errors inherent in the Julian calendar. However, the Gregorian calendar defines a year as 365 days, and because the earth actually takes 365.25635 days to revolve around the sun, a leap year is introduced every four years to cancel out the error.
LastDayOfWeek Procedure Get the last day of the week (Saturday) of the supplied date.
LastDayOfLastWeek Procedure Get the last day of the week (Saturday) of the previous week.
LastDayOfNextWeek Procedure Get the last day of the week (Saturday) of the following week.
LastDayOfWeekInMonth Procedure Get the last day (Sunday, Monday, etc.) of the month.
MonthFirstDay Procedure Get the first day of the month.
MonthFirstDayLastMonth Procedure Get the first day of the previous month.
MonthFirstDayNextMonth Procedure Get the first day of the next month.
MonthFirstWeekday Procedure Get the first weekday of the month (first day of the month that is not a Saturday or Sunday).
MonthLastDay Procedure Get the last day of the month.
MonthLastDayLastMonth Procedure Get the last day of the previous month.
MonthLastDayNextMonth Procedure Get the last day of the following month.
MonthLastWeekday Procedure Get the last weekday of the month (last day of the month that is not a Saturday or Sunday).
NextDate Procedure Adds one day to a date.
NextDOW Procedure Get the specified day of the week after the given date. e.g. NextDOW (#1/1/2013#, 4) returns the date of the Wednesday after 1/1/2013.
NextWeekday Procedure Get the next weekday (skips weekend dates). Date returned is always between Monday and Friday.
NthDayOfMonth Procedure Get the date of the Nth day (Monday, Tuesday, etc.) of the month.
PriorDate Procedure Decrements the passed date by 1.
PriorDOW Procedure Get the specified day of the week after the given date. e.g. PriorDOW (#03/28/64#, 6) returns the date of the Friday before 03/28/64)
PriorWeekday Procedure Get previous weekday (skips weekend dates).
QuarterFirstDay Procedure Get the first day of the quarter for a given date. Quarters start at the beginning of January, April, July, and October.
QuarterLastDay Procedure Get the last day of the quarter for a given date. Quarters end at the end of March, June, September, and December.
DayOfWeekName Procedure Get the day of week name for any date.
WeekNumber Procedure Get the week number as defined by the format command which counts Jan 1 as week 1, even if it's in the middle of the week.
WeekNumberStartJan Procedure Get the week number where 1 is the week including Jan 1 and end of year dates are week 1 of the following year. If the week includes Jan 1, the entire week is treated as week 1, including the end of December days. This eliminates the problem when the last week of the year is considered partially week 53 and week 1.
WeekNumberStandardized Procedure Get the week number for a specific date using ISO 8601 guidelines. Because years don't fall into 52 neat calendar weeks, rules are established by accounting and ISO 8601 guidelines. Weeks start on Monday and end on Sunday. The week number depends if the first week of the year belongs in the previous year or new year. It's considered in the new year if the new year's first week has at least 4 days. If the week includes Jan 1, the entire week is treated as week 1, including the end of December days. A calendar year can have 53 weeks if Jan 1 is on a Thursday.
WeekNumberFirstDayOfYear Procedure Get the first day of the specified year using ISO 8601 week definition. Because years don't fall into 52 neat calendar weeks, rules are established by accounting and ISO 8601 guidelines. Weeks start on Monday and end on Sunday. The week number depends if the first week of the year belongs in the previous year or new year. It's considered in the new year if the new year's first week has at least 4 days. If the week includes Jan 1, the entire week is treated as week 1, including the end of December days. A calendar year can have 53 weeks if Jan 1 is on a Thursday.

Example Code for Using Module: DateTime

' Example of modDateTime
'
' To use this example, create a new module and paste this code into it.
' Then run the procedure by putting the cursor in the procedure and pressing:
'    F5 to run it, or
'    F8 to step through it line-by-line (see the Debug menu for more options)

Private Sub Example_modDateTime()
  ' Comments: Examples of using the modDateTime module to calculate a wide range of date and time related functions in VBA and VB6.
  '           See results in the Immediate Window.

  Const cintAdd As Integer = 6

  Dim datTest1 As Date
  Dim datTest2 As Date
  Dim datTest3 As Date

  datTest1 = DateSerial(Year(Date), 12, 31)        ' Last day of this year
  datTest2 = DateSerial(Year(Date) + 4, 5, 12)     ' May 12 four years from now

  Debug.Print "Today", DayOfWeekName(Date)
  Debug.Print "Yesterday ", PriorDate(Date)
  Debug.Print "Tomorrow", NextDate(Date)
  Debug.Print "Previous weekday", PriorWeekday(Date)
  Debug.Print "Next weekday", NextWeekday(Date)
  Debug.Print "Last Sunday", PriorDOW(Date, 1)
  Debug.Print "Next Sunday", NextDOW(Date, 1)
  Debug.Print
  Debug.Print "First day of this week", FirstDayOfWeek(Date)
  Debug.Print "Last day of this week", LastDayOfWeek(Date)
  Debug.Print "First day of next week", FirstDayOfNextWeek(Date)
  Debug.Print "Last day of next week", LastDayOfNextWeek(Date)
  Debug.Print "First day of last week", FirstDayOfLastWeek(Date)
  Debug.Print "Last day of last week", LastDayOfLastWeek(Date)
  Debug.Print
  Debug.Print "First day of this month", MonthFirstDay(DatePart("m", Date), DatePart("yyyy", Date))
  Debug.Print "Last day of this month", MonthLastDay(DatePart("m", Date), DatePart("yyyy", Date))
  Debug.Print "First day of next month", MonthFirstDayNextMonth(Date)
  Debug.Print "Last day of next month", MonthLastDayNextMonth(Date)
  Debug.Print "First day of last month", MonthFirstDayLastMonth(Date)
  Debug.Print "Last day of last month", MonthLastDayLastMonth(Date)
  Debug.Print
  Debug.Print "First weekday of this month", MonthFirstWeekday(DatePart("m", Date), DatePart("yyyy", Date))
  Debug.Print "Last weekday of this month", MonthLastWeekday(DatePart("m", Date), DatePart("yyyy", Date))
  Debug.Print "2nd Sunday of this month", NthDayOfMonth(DatePart("m", Date), DatePart("yyyy", Date), 2, 1)
  Debug.Print "Last Sunday in this month", LastDayOfWeekInMonth(DatePart("m", Date), DatePart("yyyy", Date), 1)
  Debug.Print "First day of the quarter", QuarterFirstDay(Date)
  Debug.Print "Last day of the quarter", QuarterLastDay(Date)
  Debug.Print
  Debug.Print "Today plus 30 weekdays is ", AddWeekdays(Date, 30)
  Debug.Print cintAdd & " hours from now is ", AddHours(Now, cintAdd)
  Debug.Print cintAdd & " days from now is ", AddDays(Date, cintAdd)
  Debug.Print cintAdd & " weeks from now is ", AddWeeks(Date, cintAdd)
  Debug.Print cintAdd & " months from now is ", AddMonths(Date, cintAdd)
  Debug.Print cintAdd & " years from now is ", AddYears(Date, cintAdd)

  Debug.Print
  Debug.Print "Check for leap year:"
  Debug.Print "Days in February " & Year(Date), DaysInMonth(2, Year(Date))
  Debug.Print "Days in February " & Year(Date) + 1, DaysInMonth(2, Year(Date) + 1)
  Debug.Print "Days in February " & Year(Date) + 2, DaysInMonth(2, Year(Date) + 2)
  Debug.Print "Days in February " & Year(Date) + 3, DaysInMonth(2, Year(Date) + 3)
  Debug.Print

  Debug.Print "Seconds in one day:", DiffSeconds(datTest1, datTest1 + 1)
  Debug.Print "The difference in time between 1:00 AM and the now is: ", DiffTime(#1:00:00 AM#, Now, True)
  Debug.Print

  ' Set test date to two days from today at 2:01:30 AM
  datTest3 = (Date + 2) + #2:01:15 AM#
  Debug.Print "Full       minutes between today and " & datTest3, DiffMinutes(Date, datTest3)
  Debug.Print "Fractional minutes between today and " & datTest3, DiffMinutesFractional(Date, datTest3)
  Debug.Print "Full       days between today and " & datTest3, DiffDays(Date, datTest3)
  Debug.Print "Fractional days between today and " & datTest3, DiffDaysFractional(Date, datTest3)
  Debug.Print

  Debug.Print "Weekdays between today and " & datTest1, DiffWeekdays(Date, datTest1)
  Debug.Print "Weeks between " & datTest1 & " and " & datTest2, DiffWeeks(datTest1, datTest2)
  Debug.Print "Months between " & datTest1 & " and " & datTest2, DiffMonths(datTest1, datTest2)
  Debug.Print

  Debug.Print "2018 " & IIf(IsLeapYear(2018), "is", "isn't") & " a leap year."
  Debug.Print "2020 " & IIf(IsLeapYear(2020), "is", "isn't") & " a leap year."
  Debug.Print "Your randomly generated date, between 1950 and 2020: " & GetRandomDate(#1/1/1950#, #12/31/2020#)
  Debug.Print

  ' Example for GetDatePart()
  ' Return the specific quarter that a date occurs in.
  Debug.Print "Current Quarter: " & GetDatePart(Date, theQuarter)

  Debug.Print "If Jan. 1 is the start of Week 1, " & datTest1 & " is in week #" & WeekNumber(datTest1)
  Debug.Print "By accounting/ISO guidelines, " & datTest1 & " is in week #" & WeekNumberStandardized(datTest1)
  Debug.Print "Counting the full week of Jan. 1 as Week 1, " & datTest1 & " is in week #" & WeekNumberStartJan(datTest1)
  Debug.Print

  ' Show the difference between two dates in words: 3 Months, 27 Days, 21 Hours, 58 Minutes, 45 Seconds
  Debug.Print "Difference between " & datTest1 & " and " & datTest3, DiffDatesFormatted_CalculatedByCalendar(datTest1, datTest3, True)
  ' Show the difference between two dates in this format: Years:Months:Days:Hours:Minutes:Seconds
  Debug.Print "Difference between " & datTest1 & " and " & datTest3, DiffDatesFormatted_CalculatedByCalendar(datTest1, datTest3, False)
  ' Show shorter time difference
  Debug.Print "Difference between " & Date & " and " & datTest3, DiffDatesFormatted_CalculatedByCalendar(Date, datTest3, True)
  Debug.Print "Difference between " & Date & " and " & datTest3, DiffDatesFormatted_CalculatedByCalendar(Date, datTest3, False)
  Debug.Print

  ' Example for FormatAsYMDHMS()
  '     38959290 seconds = 1 year, 2 months, 25 days, 2 hours, 1 minute, 30 seconds
  '     38959290 = 31536000 + ((31536000/12) * 2) + (86400 * 25) + (3600 * 2) + 60 + 30
  '     1 Year = 365 days
  ' Show the difference between two dates in words or yy:mm:dd:hh:mm:ss format
  Debug.Print "38959290 Seconds is", DiffDatesFormatted_CalculatedBySecondsPerYear(38959290, numberOfSeconds, True)
  Debug.Print "38959290 Seconds is", DiffDatesFormatted_CalculatedBySecondsPerYear(38959290, numberOfSeconds, False)
  Debug.Print "1000 Minutes is", DiffDatesFormatted_CalculatedBySecondsPerYear(1000, numberOfMinutes, True)
  Debug.Print "1000 Minutes is", DiffDatesFormatted_CalculatedBySecondsPerYear(1000, numberOfMinutes, False)

End Sub

Microsoft Access Module LibraryOverview of Total Visual SourceBook

The source code in Total Visual SourceBook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Additional Resources