.png)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calculate Age (Days) Between 2 Dates
I have two dates: CreateDate and CloseDate and would like to calculate the age of the record that contains these two dates. My questions are:
- What is the expression for calculating the date difference to determine the age of each record?
- How do I incorporate that into the load code?
Thanks.
Jeff
- « Previous Replies
-
- 1
- 2
- Next Replies »

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can use the networkDays(startdate,enddate,[holidays]) to calculate the age. It calculates number of working days between startdate(should be less than the end date) and enddate,holiday dates is optional you can include it if required.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Jeff,
Simply use this in load script CloseDate-CreateDate as TotalDay
Regards,
Sokkorn

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Jeffrey, If you want calculate the age in no of days, you can directly subtract the date values like below:
Num(CloseDate) - Num(CreateDate) AS Age_in_Days
If you want exclude the Holidays please use Networkdays function.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
You can use the interval Function.
interval(expression [ , format-code ])
The interval function formats the expression as a time interval according to the string given as a format-code. If the format code is omitted, the time format set in the operating system is used. Intervals may be formatted as a time, as days or as a combination of days, hours, minutes, seconds and fractions of seconds.
Examples:
interval( A-B, 'D hh:mm' ) where A=97-08-06 09:00:00 and B=96-08-06 00:00:00 returns:
String | 365 09:00 |
Number | 365.375 |
-Nilesh

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Try like this
Data:
Load
*,
Interval(EndDate - StartDate, 'DD') AS Age
FROM DateSource;
Replace EndDate and StartDate with your actual field names.
Regards,
Jagan.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here you can do in two ways one using Interval and using DateDiff .
Data:
Load
*,
Interval(EndDate - StartDate, 'DD') AS Age
Resident oldData;
Replace EndDate and StartDate with your actual field names.
//Function to calculate Date Difference in script
SET DateDiff = Num(((StartDate) - (EndDate)) + 1);
Now, we can use the MonthDiff as a function
Data:
LOAD
UniqueNo,
StartDate,
EndDate,
($(DateDiff(StartDate, EndDate)) AS DateDifference
FROM DataSource;
In the above load statement we are calculating the months difference between two dates and arriving it as a new column.
We can also use the MonthDiff in expressions also like below
=$(DateDiff(Today(), Today() + 65))
Hope this helps others.
Regards,
Sunil
.png)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sokkorn:
That was exaclty what I wanted. Thank you.
Now what I want to do is to subtract the current date (today) from the "Create Date" (the age of open records) and then create a field called "Days Open". And in the field I want, based on the calculated age, inserted either "<30", "31-60", "61-90", "91-120" and ">120".
I will then use the "Days Open" data into a bar chart that will count the number of each range of open records. Closed records (with a closed date) do not get counted.
I know I want to use Interval Match, but not sure how to incorporate.
Jeff
'
.png)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
or actually use an if/then statement.
Jeff

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can use the class function to group the ages.

- « Previous Replies
-
- 1
- 2
- Next Replies »