Skip to main content
Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for 
Search instead for 
Did you mean: 
bobbydave
Creator III
Creator III

Interval function

Hey guys,

I am trying to get the amount of months from

1. REGISTERED AND COVER START DATE

2. NOTIFICATION DATE and INCIDENT DATE

I have been using the Interval function to no avail.

Attached is the results that I should get.

The difference between 1. is shown as POL_TO_INC_M and the difference in 2. is show as INC_TO_NOTIFY_M

fabs(Interval(REGISTERED - COVER_START_DATE, 'M') ) as POL_TO_INC_M,
fabs(Interval(NOTIFICATION_DATE - INCIDENT_DATE, 'M')) as INC_TO_NOTIFY_M  

Any help appreciated

1 Solution

Accepted Solutions
sunny_talwar

When I use this script:

Table:

LOAD *,

  ((Year(REGISTERED)*12) + Month(REGISTERED)) - ((Year(COVER_START_DATE)*12) + Month(COVER_START_DATE)) as POL_TO_INC_M,

  ((Year(NOTIFICATION_DATE)*12) + Month(NOTIFICATION_DATE)) - ((Year(INCIDENT_DATE)*12) + Month(INCIDENT_DATE)) as INC_TO_NOTIFY_M;

LOAD REGISTERED,

    COVER_START_DATE,

    INCIDENT_DATE,

    NOTIFICATION_DATE,

    POL_TO_INC_M as POL_TO_INC_M_4M_EXCEL,

    INC_TO_NOTIFY_M as INC_TO_NOTIFY_M_4M_EXCEL

FROM

[Test (9).xlsx]

(ooxml, embedded labels, table is Sheet1);

I get this:

Capture.PNG

View solution in original post

5 Replies
sunny_talwar

Try the expression provided by Rob here:

How Do I Get the Number of Months Between Two Dates?

sunny_talwar

May be like this:

(Year(REGISTERED)*12) + Month(REGISTERED)) - (Year(COVER_START_DATE)*12) + Month(COVER_START_DATE)) as POL_TO_INC_M,

(Year(NOTIFICATION_DATE)*12) + Month(NOTIFICATION_DATE)) - (Year(INCIDENT_DATE)*12) + Month(INCIDENT_DATE)) as INC_TO_NOTIFY_M,

sunny_talwar

When I use this script:

Table:

LOAD *,

  ((Year(REGISTERED)*12) + Month(REGISTERED)) - ((Year(COVER_START_DATE)*12) + Month(COVER_START_DATE)) as POL_TO_INC_M,

  ((Year(NOTIFICATION_DATE)*12) + Month(NOTIFICATION_DATE)) - ((Year(INCIDENT_DATE)*12) + Month(INCIDENT_DATE)) as INC_TO_NOTIFY_M;

LOAD REGISTERED,

    COVER_START_DATE,

    INCIDENT_DATE,

    NOTIFICATION_DATE,

    POL_TO_INC_M as POL_TO_INC_M_4M_EXCEL,

    INC_TO_NOTIFY_M as INC_TO_NOTIFY_M_4M_EXCEL

FROM

[Test (9).xlsx]

(ooxml, embedded labels, table is Sheet1);

I get this:

Capture.PNG

bobbydave
Creator III
Creator III
Author

This works perfect Sunny, but is there a reason why the Interval function wouldn't work?

sunny_talwar

Interval function can only output, days hours months and seconds. Unfortunately, it doesn't work for Months and years. If you want the days difference or hours difference, then I would recommend using the Interval function.