Calculate Age (Days) Between 2 Dates

[jmonroe918]

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:

1. What is the expression for calculating the date difference to determine the age of each record?
2. How do I incorporate that into the load code?

Thanks.

Jeff

[brindlogcool]

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.

[Sokkorn]

Hi Jeff,

Simply use this in load script CloseDate-CreateDate as TotalDay

Regards,

Sokkorn

[#4]

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.

[nilesh_gangurde]

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

[jagan]

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.

[sunilkumarqv]

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

[jmonroe918]

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
'

[jmonroe918]

or actually use an if/then statement.

Jeff

[brindlogcool]

You can use the class function to group  the ages.