Creating bandings 0-6, 6-12, etc

[#1]

So i have found out how many working days between two dates.. 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

Thanks

[#2 · Accepted solution]

Hi Andrew,

you are trying to do a second IF as part of your AND statement when you want both conditions,

>6 And <12

not >6 And If <12

so the number of parameters on your IF's are going wrong.

You are evaluating these in sequence though, you don't actually need to the second condition in each IF,

basically anything under 6 months is caught in your first true section, so the only possible false has to be greater than 6 and so you only then need to check less than 12 etc..

So I think the below should work for you

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 <=12,'6-12mts',

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 <=24,'18-24mts',

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

hope that helps

Joe

[#3]

Thanks very much.. that worked... don't know why i was doing it that way... i had my excel cap on there...

[#4]

No problem,

I should probably add that, you could do this with a simple mapping table instead of all the nested if statements if you wanted.

Just create a new mapping table along the lines of

Month, Group

1, 0-6 mths

2, 0-6 mths

3, 0-6 mths

4, 0-6 mths

5, 0-6 mths

6, 0-6 mths

7, 6-12 mths

etc

etc

then you can just use applymap on you base function(with floor)

Applymap( 'Your mapping field here', Floor(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30) , 'Error' ) As banding

just a thought really

hope that helps

Joe