Finding months between two dates

[#1]

Hi All

I am struggling to solve my problem and would appreciate some help.

I have two date fields which are formatted differently in my source data.

One is a notification date which is formatted like so 01/01/2015

The other is report month which is formatted like this 201505

Now i need to find out the months between the two.

So it would be 4 months for the above.

I am hoping to work this out in the script as i want the answer to be a filed which i can use in my data and also create another field in my script which would be banding.

Any ideas.

Thanks

Andrew

[PrashantSangle]

Hi,

TRY LIKE

=

mONTH(date#(201505,'YYYYMM'))-MONTH(date#('01/01/2015','DD/MM/YYYY'))

Regards

[#3]

Hi, Thanks for the answer, Unfortunately that doesn't work. The date which is actually formatted like 201505 is text not an actual date. So there is no code actually behind the text so as to do the sum.

I think what i will have to do is take the month and the year off the 201505 and create a date by sticking a 01  in front of it like 01/05/2015 then do the sum.. Im struggling with the formula though.

[kiranmanoharrode]

Hi,

TRY LIKE

=

Num(Month(date#(201505,'YYYYMM')))-Num(MONTH(date#('01/01/2015','DD/MM/YYYY')))

Regards

Kiran

[PrashantSangle]

Hi,

If it is text then Month(Date#(datefield,'YYYYMM')) will definately work for you

also try with right(datefield,2)

Regards

[PradeepReddy]

try something like this... using makedate() function

=num(month(makedate(left(201505,4),Right(201505,2),1)))-num(MONTH(date#('01/01/2015','DD/MM/YYYY')))

[Anonymous]

Hi,

Try below code in script:

LOAD Num(Month(Date(ReportDates)-Date(NotificationDates))) as Months;
LOAD
Date(Date#(NotificationDate,'DD/MM/YYYY'),'DD/MM/YYYY') as NotificationDates,
Date(makedate(left(ReportDate,4),Right(ReportDate,2),1),'DD/MM/YYYY') as ReportDates;
LOAD * Inline
[ReportDate,NotificationDate
201505,01/01/2015
];

[#8]

Hi All,

Thanks for you help with this... i have found the answer...

Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY') as newdate,

     NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))

[#9]

I am now having a problem banding the above result. So i have found out how many working days between the two dates above.. but now i want to band the results by months.

So 0-6mts, 6-12mths, 12-18mts, 18-24mts, 24-36mts, >36mts

Below is my script but im getting errors say you are only allowed 3 if conditions.. I didnt know that..

Any help

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=6,'0-6mts',

      if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >6 and if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=12,'6-12mts',

      if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >12 and if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=18,'12-18mts',

      if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >18 and if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=24,'18-24mts',

      if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >24 and if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=36,'24-36',

      if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >36,'>36mts','Error'))))))) as Banding