Interval function

[bobbydave]

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

[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 · Accepted solution]

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:

[bobbydave]

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.