Calculating dates in Visual Basic 6

Posted on 2007-08-02
Last Modified: 2012-05-05
Lo folks,

I need an easy way of doing this, I could write some massive bit of code so my program can understand dates and times, and months and years... but if theres an easier way I want to know!

Say I tell my program a date, say for example todays date... 2/8/2007 (or 20070802 in YYYYMMDD) - I'd like my program to tell me what the date was X amount of days earlier.

Anyknow know of any easy way of doing this is?
Question by:netcentraltech
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hello netcentraltech,

    You can do date arithmetic:

    DateVariable = #2 Aug 2007#
    Debug.Print "Yesterday was: " & DateVariable - 1


    LVL 53

    Expert Comment

    Public Function AdjustDays(FutureDate As Boolean, _
      NumberOfDays As Integer, Optional RelativeTo As Variant) _
      As Date



    'FutureDate: True if you want a date in the future
    'relative to the given date, false if you want a date in the past

    'NumberOfDays: How Many Days in the Future or Past you want
    'to add or subtract from the given date

    'RelativeTo (Optional): The given date; defaults to Today

    'EXAMPLE: returns date 30 days from today

    'dim dDate as date
    'dDate = AdjustDays(True, 30)
       Dim dDate As Date
       Dim iDirection As Integer
       If IsMissing(RelativeTo) Then
           dDate = Format(Now, "Short Date")
       ElseIf Not IsDate(RelativeTo) Then
           Err.Raise 20000, , "Date required"
           Exit Function
           dDate = Format(CDate(RelativeTo), "Short Date")
       End If
       iDirection = IIf(FutureDate, 1, -1)
      AdjustDays = DateAdd("d", NumberOfDays * iDirection, dDate)
    End Function

    LVL 92

    Expert Comment

    by:Patrick Matthews

    Not a criticism, as I just may not be getting it--what is the advantage of using that UDF
    over simple arithmetic or just using the native DateDiff() function?  I ask only because
    I want to learn.


    LVL 53

    Expert Comment

    If you put it in a function, it's more general. So you can use it from more places in your code.
    For example: somewhere in the program you need to calculate 5 days before the current date end somewhere else you need to calculate 5 days further.
    In that case you just have to call the function with the right params.

    Off course you can also use the native datediff-function everytime..
    LVL 38

    Accepted Solution

    Date arithmetic as per Patrick's comment above is an option and works because VB holds dates internally as doubles, with the integer part representing days, and the fractional part representing parts of days.

    The DateAdd function is a very flexible way to add any amount of time to a date/time variable.  I have a few issues with the function that Dhaest has found.  
    -The unnecessary conversion from variant and to string.  
    -Why add a boolean parameter, when entering a positive or negative integer should suffice
    -Overly complicated for achieving a simple result.

    Simply calling DateAdd with the number of days will return the value needed.

    Debug.Print "Three days ago it was " & DateAdd("d", -3, Date)
    LVL 85

    Expert Comment

    by:Mike Tomlinson
    DateAdd() is definitely the way to go as it keeps the code easy to understand.

    While "Date arithmetic" does work, not everyone can look at code like that and immediately understand what is happening...especially when you are not simply adding days.

    Here is the documentation for DateAdd():

    The other intervals you can use are:

    yyyy = Year
    q = Quarter
    m = Month
    y = Day of year
    d =  Day
    w = Weekday
    ww = Week of year
    h = Hour
    n = Minute
    s = Second
    (Some of the intervals are actually equivalent to each other)
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Idle_Mind said:
    >>While "Date arithmetic" does work, not everyone can look at code like that and immediately
    >>understand what is happening...especially when you are not simply adding days.

    No disagreement here.  If I needed to add or subtract an hour, I would not add or subtract 1/24.
    I'd use DateAdd.  If it's a matter of adding days, well, I'm lazy by nature :)

    LVL 53

    Expert Comment

    Can you give some feedback if one of the answers was sufficient or do you still have any problems ?

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now