Gallery

Snoozing at the OASIS

Posted on 28 Jan 2009 at 17:07

This month, Simon Jones looks into the maintenance of the ODF standard, and sizes up the new Outlook add-in Xobni.

The DateDiff() function in Visual Basic and SQL Server takes different parameters in a different order and can calculate weeks, quarters, hours, minutes and seconds as well as days, months and years between the two dates. You can call this from a cell in an Excel workbook if you wrap it up into your own public macro function.

For instance, if you put this code into a new Module in your workbook:

Public Function DateDifference(Interval As String, Date1 As Date, Date2 As Date)

DateDifference = DateDiff(Interval, Date1, Date2, vbMonday, vbFirstJan1)

End Function

then you can use this customised DateDifference() function to pass the relevant parameters on to VBA's DateDiff() function and return the result to a cell, for example = DateDifference("q", D1, D2).

The two optional parameters in DateDiff() state what the first day of the week is and when the first week of the year starts. These can be set explicitly, or else set to follow the Control Panel settings for the current computer. Beware that different Control Panel settings may then change the result of the DateDiff() function, and you might get different results on different PCs - if you need consistent results, fix these parameters to known values. Also, depending on these settings and the dates concerned, DateDif() and DateDiff() can yield different numbers of months between dates.