Solved

# Calculating dates in Visual Basic 6

Posted on 2007-08-02
134 Views
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?
0
Question by:netcentraltech

LVL 92

Expert Comment

Hello netcentraltech,

You can do date arithmetic:

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

Regards,

Patrick
0

LVL 53

Expert Comment

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

'***************************************
'PURPOSE: RETURNS A DATE X NUMBER OF DAYS BEFORE OR AFTER
'A GIVEN DATE

'PARAMETERS:

'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
'***************************************
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
Else
dDate = Format(CDate(RelativeTo), "Short Date")
End If
iDirection = IIf(FutureDate, 1, -1)
End Function

Source: http://www.freevbcode.com/ShowCode.Asp?ID=980
0

LVL 92

Expert Comment

Dhaest,

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.

Regards,

Patrick
0

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

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)
0

LVL 85

Expert Comment

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():
http://msdn2.microsoft.com/en-us/library/cb7z8yf9.aspx

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)
0

LVL 92

Expert Comment

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 :)

Patrick
0

LVL 53

Expert Comment

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

## Featured Post

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…