DateDiff()

Brian Element

Syntax: DateDiff(Interval as String, Date1 as Date, Date2 as Date, Optional DayOfWeek, Optional WeekOfYear) as Long

Parameters:

Interval - There are 10 different types of intervals.  Depending on the interval the DateDiff() well return the number of units in the Interval.  Below is a list of the 10 items.  In the script below I used constants to better define them.

Parameter Description
"d" Day
"y" Day of year
"h" Hour
"n" Minute
"m" Month
"q" Quarter
"s" Second
"w" Week
"ww" Calendar Week
"yyyy" Year

Date1 - The first date/time value you want to use in the calculation.

Date2 - The second ate/time value you want to use in the calculation.

DayOfWeek - This is optional and allows you to decide on what day the week starts on.  There are 8 options.

Parameter Descritpion
0 First day of week specified in system settings
1 Sunday (default)
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday

WeekOfYear - This is optional and allows you to decide how the first week of the year is calculated.  There are 4 options.

Parameter Description
0 First week of year specified in system settings
1 Week in which Jan 1 occurs (default)
2 Week that has at least four days in the new year
3 First full week in the new year

Notes: The functions returns both positive and negative number depending on how the dates are set-up.  You can also use DateTime fields (which are not native to IDEA but can be stored as a character field).  The last two examples show how you can also include time into the calculation.

Script
Const DATEINTERVAL_DAY = "d" 'day
Const DATEINTERVAL_DAY_OF_YEAR = "y" 'day
Const DATEINTERVAL_HOUR = "h" 'hour
Const DATEINTERVAL_MINUTE = "n" 'minute
Const DATEINTERVAL_MONTH = "m" 'month
Const DATEINTERVAL_QUARTER = "q" 'quarter
Const DATEINTERVAL_SECOND = "s" 'Second
Const DATEINTERVAL_WEEK_DAY = "w" 'week
Const DATEINTERVAL_WEEK_OF_YEAR = "ww" 'calendar week
Const DATEINTERVAL_YEAR = "yyyy" 'Year

Const FIRST_DAY_OF_WEEK_SYSTEM = 0 'first day of week specified in system settings
Const FIRST_DAY_OF_WEEK_SUNDAY = 1 'Sunday
Const FIRST_DAY_OF_WEEK_MONDAY = 2 'Monday
Const FIRST_DAY_OF_WEEK_TUESDAY = 3 'Tuesday
Const FIRST_DAY_OF_WEEK_WEDNESDAY = 4 'Wednesday
Const FIRST_DAY_OF_WEEK_THURSDAY = 5 'Thursday
Const FIRST_DAY_OF_WEEK_FRIDAY = 6 'Friday
Const FIRST_DAY_OF_WEEK_SATURDAY = 7 'Saturday

Const FIRST_WEEK_OF_YEAR_SYSTEM = 0
Const FIRST_WEEK_OF_YEAR_JAN_1 = 1
Const FIRST_WEEK_OF_YEAR_FIRST_FOUR_DAYS = 2
Const FIRST_WEEK_OF_YEAR_FIRST_FULL_WEEK = 3


Sub Main
	Dim datTime1 As Date
	Dim datTime2 As Date
		
	datTime1 = #1/4/2009#
	datTime2 = #1/9/2009#
	
	MsgBox "Days: " & DateDiff(DATEINTERVAL_DAY, datTime1, datTime2) 'returns 5 days
	MsgBox "Days of year: " & DateDiff(DATEINTERVAL_DAY_OF_YEAR, datTime1, datTime2) 'returns 5 days
	MsgBox "Hours: " & DateDiff(DATEINTERVAL_HOUR, datTime1, datTime2) 'returns 120 hours (5 days * 24 hours)
	MsgBox "Minutes: " & DateDiff(DATEINTERVAL_MINUTE, datTime1, datTime2) 'returns 7200 minutes (120 hours * 60 minutes)
	MsgBox "Seconds: " & DateDiff(DATEINTERVAL_SECOND, datTime1, datTime2) 'returns 432,000 seconds (7200 minutes * 60 seconds)
	
	datTime1 = #1/4/2010#
	datTime2 = #1/4/2013#
	
	MsgBox "Years: " & DateDiff(DATEINTERVAL_YEAR, datTime1, datTime2) 'returns 3 years
	MsgBox "Months: " & DateDiff(DATEINTERVAL_MONTH, datTime1, datTime2) 'returns 36 months (3 years * 12 months)
	MsgBox "Weeks: " & DateDiff(DATEINTERVAL_WEEK_DAY, datTime1, datTime2) 'returns 156 weeks (3 years * 52 weeks)
	MsgBox "Weeks of year: " & DateDiff(DATEINTERVAL_WEEK_OF_YEAR, datTime1, datTime2) 'returns 156 weeks (3 years * 52 weeks)
	MsgBox "Quarters: " & DateDiff(DATEINTERVAL_QUARTER, datTime1, datTime2) 'returns 12 quarters (3 years * 4 quarters)
	
	'example of using the first parameter of setting the day of the week

	datTime1 = #12/31/2009#
	datTime2 = #01/05/2010#
	
	MsgBox "Weeks with first day of week being Monday: " & DateDiff(DATEINTERVAL_WEEK_DAY, datTime1, datTime2, FIRST_DAY_OF_WEEK_MONDAY ) '156
	MsgBox "Weeks of year with first day of week being Monday: " & DateDiff(DATEINTERVAL_WEEK_OF_YEAR, datTime1, datTime2, FIRST_DAY_OF_WEEK_MONDAY ) '157
	
	'can also be used using date time fields.
	datTime1 = "31-Jan-09 00:00:00"
	datTime2 = "31-Jan-09 01:00:00"
	
	MsgBox "Hours: " & DateDiff(DATEINTERVAL_HOUR, datTime1, datTime2) 'returns 1 hours 
	MsgBox "Minutes: " & DateDiff(DATEINTERVAL_MINUTE, datTime1, datTime2) 'returns 60 minutes 
	MsgBox "Seconds: " & DateDiff(DATEINTERVAL_SECOND, datTime1, datTime2) 'returns 3600seconds 

	
End Sub

 

IDEAScript Language

KrisW Wed, 05/31/2017 - 17:45

I have stepped through the code/script, but this is using VBA to determine DateDiff between 2 input dates. Has anyone written a custom function for IDEA that does this so that the DateTime values in 2 columns of a table/database can be compared & the difference reported accounding to the incremental value passed?

jithukh@gmail.com Sun, 07/15/2018 - 04:49

Hi,
started using idea a week back and trying to use 'Datediff' within parameter while appending new virtual numeric field. Below formula throws Syntax error.
@datediff("d", " date1 ", " date2 ") 
Any help would be very helpful as I'm beginner to IDEA script.
 

Brian Element Sun, 07/15/2018 - 08:03

Good morning, before I answer your question I just want to make sure I understand, are you building an IDEAScript or are you in the equation editor.  The reason I ask is the @ symbol is used in the equation editor.  Or are you talking about the eqn used to create the virtual numeric field.  If you give me a bit more info I will be in a better spot to help you out.

jithukh@gmail.com Tue, 07/17/2018 - 02:33

Hi Brian, 
Sorry for the confusion. I was trying to create a virtual numeric field. My goal was to create field manually and record macro so that it can be used. Please let me know if you need any more info. Thanks..
 

Brian Element Tue, 07/17/2018 - 06:28

What you want to use in the equation editor is the @age() function that will give you the difference in days.

@Age

Returns the number of days between the two specified dates. Both dates must be in IDEA Date format (YYYYMMDD). Either or both dates may be fields or constants. It can be used when auditing the efficiency or responsiveness of a department (length of time to collect cash or process an order to dispatch).

Syntax

@Age(Date1, Date2)

Parameters

Date1 - The first date. It can be a Date field or a date constant.

Date2 - The second date. The first date (Date1) should be later than the second date (Date2) for the resulting number of days to be positive. 

jithukh@gmail.com Wed, 07/18/2018 - 01:58

Thank you very much Brian.
This helps to calculate days between to dates. Is there any built in function to calculate the difference by month/year (other than 'Datediff'). 
Also I'm a beginner to IDEA. Is the best way to master IDEA macro is by learning Excel VBA. Your suggestions would be very helpful. Thanks!!!

Brian Element Wed, 07/18/2018 - 19:06

You can't use the Datediff as this is a visual basic command and won't work in the equation editor.  I did put something together that might works: @if(@Year(DATE2) > @Year(DATE1), (@Year(date2) - @year(date1)) * 12 + (@Month(DATE2) - @Month(DATE1)), @Month(DATE2) - @Month(DATE1))

You have two date fields where the DATE2 is larger than the DATE1.  If the years are different is multiplies them by 12 and then takes the months, if it is the same year then it just takes the month difference.  Let me know if this helps you out.

Brian Element Thu, 07/19/2018 - 05:38

If you want to learn IDEAScript know Excel VBA is good because they are similar but not exact, there are a few differences.  What I would suggest is look through scripts that are available and see how others have done it, ask questions on this site.  On the front page I have a link to a book called Mastering IDEAScript, it is a bit pricey now but it might be worth the investment.  I also have a few videos up on creating an IDEA script.  CaseWare also has a 3 day training on IDEAScripting that you might want to check out.  It is a speciality language so the resources like you have for Excel aren't there, that is one of the reasons I created this site is to help people out.

Sedem Thu, 07/26/2018 - 11:47

I am new too in ideascripting and first will like to congratulate you for the wonderful work you are doing here, been reading some of your examples to guide myself. Apparently I have date column “DD/MM/YYYY” will like to know the exact date after 60years From this date?

avikerem Mon, 03/02/2020 - 05:28

Hi Brian
I would like to work with variables of type date and would like to do some manipulations on these variables. For example to add 180 days to the variable and pass the result to datediff() or to get the current day, month and year as integers or strings and build a date variable from these components and pass the result to datediff(). Etc.
I think that the equation editor date conversion routines, such as ctod or dtoc, with an H as prefix, don't work.
Any insight?
Thanks a lotand have a wonderful week
Avi Kerem

Brian Element Mon, 03/02/2020 - 07:44

Hi Avi,

Doing something like that directly in the equation editor is not easy, best to do it as a script or a custom function.  Here is some sample code that takes the current date and adds 180 days to it.


Sub Main
	Dim iYear As Integer
	Dim iMonth As Integer
	Dim iDay As Integer
	Dim currentDate As String
	
	currentDate = Now()
	
	iYear = Year(currentDate)
	iMonth = Month(currentDate)
	iDay = Day(currentDate)
	
	MsgBox currentDate
	MsgBox DateSerial(iYear, iMonth, iDay + 180)
End Sub

akwatdok Wed, 09/01/2021 - 10:12

Brian,
 I want to get the time difference between two dates expressed as in two fields as 30/08/2021 20:11:05 and 31/08/2021 21:09:10