DATEDIFF |
DATEDIFF(interval, date1, date2 [,firstdayofweek] [,firstweekofyear]) |
Returns the number of a given time interval between two specified dates (Long). |
interval | The interval of time you want to calculate (String): "yyyy","YYYY" = year "q","Q" = quarter "m","M" = month "y","Y" = day of year "d","D" = day "w","W" = weekday "ww","WW" = week "h","H" = hour "n","N" = minute "s","S" = second |
date1 | The start date (Date). |
date2 | The finish date (Date). |
firstdayofweek | (Optional) A vbDayOfWeek constant that specifies the first day of the week: 0 = vbUseSystemDayofWeek 1 = vbSunday (default) 2 = vbMonday 3 = vbTuesday 4 = vbWednesday 5 = vbThursday 6 = vbFriday 7 = vbSaturday |
firstweekofyear | (Optional) A vbFirstWeekOfYear constant that specifies the first week of the year: 0 = vbUseSystem 1 = vbFirstJan1 (default) 2 = vbFirstFourDays 3 = vbFirstFullWeek |
REMARKS |
* If "interval" = "w", the number of weeks is based on how many days. 7 days = 1 week. * If "interval" = "ww", the number of weeks is based on how many Sundays there are. * If "interval" = "ww" and "date1" falls on a Sunday, it will not get included/counted. * If "interval" = "ww" and "date2" falls on a Sunday, it will get included/counted. * If "date1" or "date2" are enclosed in double quotation marks and the year is not entered then the current year is used. * If "date1" > "date2" then a negative number is returned. * If "firstdayofweek" is left blank, then 1 is used (ie Sunday). * If "firstweekofyear" is left blank, then the first week is assumed to be the week in which January 1 occurs. * The "firstdayofweek" is only relevant if the "interval" is either "w" or "ww". * This function can be used to determine how many specified time intervals exist between two dates. * You can use the DATE function to return the current system date. * You can use the DATEADD function to return the date with a specified time interval added. * You can use the DATEPART function to return the specified part of a given date. * You can use the DATESERIAL function to return the date given a year, month and day. * You can use the DATEVALUE function to return the date given a string representation of a date. * The equivalent Excel function is Application.WorksheetFunction.DATEDIF * The equivalent .NET function is Microsoft.VisualBasic.DateAndTime.DateDiff * For the Microsoft documentation refer to learn.microsoft.com |
'Return number of days between
Debug.Print DateDiff("d", "1 Jan 2024", "1 Apr 2024") '= 91
Debug.Print DateDiff("y", "1 Jan 2024", "1 Apr 2024") '= 91
'Return number of weeks (based on 7 days)
Debug.Print DateDiff("w", "1 Jan 2024", "2 Jan 2024") '= 0
Debug.Print DateDiff("w", "1 Jan 2024", "3 Jan 2024") '= 0
Debug.Print DateDiff("w", "1 Jan 2024", "7 Jan 2024") '= 0
Debug.Print DateDiff("w", "1 Jan 2024", "8 Jan 2024") '= 1
Debug.Print DateDiff("w", "1 Jan 2024", "14 Jan 2024") '= 1
Debug.Print DateDiff("w", "1 Jan 2024", "15 Jan 2024") '= 2
Debug.Print DateDiff("w", "4 Jan 2024", "7 Jan 2024") '= 0
Debug.Print DateDiff("w", "4 Jan 2024", "10 Jan 2024") '= 0
Debug.Print DateDiff("w", "4 Jan 2024", "11 Jan 2024") '= 1
Debug.Print DateDiff("w", "4 Jan 2024", "17 Jan 2024") '= 1
Debug.Print DateDiff("w", "4 Jan 2024", "18 Jan 2024") '= 2
'Return number of weeks (based on the specific weekday)
'6 Jan 2024 = Saturday
'7 Jan 2024 = Sunday
'8 Jan 2024 = Monday
Debug.Print DateDiff("ww", "6 Jan 2024", "7 Jan 2024") '= 1
Debug.Print DateDiff("ww", "6 Jan 2024", "8 Jan 2024") '= 1
Debug.Print DateDiff("ww", "6 Jan 2024", "12 Jan 2024") '= 1
Debug.Print DateDiff("ww", "6 Jan 2024", "13 Jan 2024") '= 1
Debug.Print DateDiff("ww", "6 Jan 2024", "19 Jan 2024") '= 2
Debug.Print DateDiff("ww", "6 Jan 2024", "20 Jan 2024") '= 2
Debug.Print DateDiff("ww", "9 Jan 2024", "12 Jan 2024") '= 0
Debug.Print DateDiff("ww", "9 Jan 2024", "15 Jan 2024") '= 1
Debug.Print DateDiff("ww", "9 Jan 2024", "16 Jan 2024") '= 1
Debug.Print DateDiff("ww", "9 Jan 2024", "22 Jan 2024") '= 2
Debug.Print DateDiff("ww", "9 Jan 2024", "23 Jan 2024") '= 2
Debug.Print DateDiff("d", VBA.Date(), "31/12/2024", 3, 0) '= 317
Debug.Print DateDiff("d", VBA.Date(), "31/12/2005", 2, 0) '= -6623
Debug.Print DateDiff("yyyy", "31 Dec 2023", "1 Jan 2024") '= 1
Debug.Print DateDiff("d", #12/1/2024#, #12/1/2023#, 0, 0) '= -366 - 2024 is a leap year so Feb has 29 days
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top