Separating a range of dates into different sections

[#1]

Hi All

I have a range of "Next Chase Dates" (NCD), some of these will be in the past, i.e. missed, some will be within the 7 days, some 8 - 14 days and some greater than 14 days.

How do I put the below into the script and give each section a name:-

If the NCD is less than today then call it “Expired”

If the NCD is today or within the next 7 days then call it “0 – 7 Days”

If the NCD  is between 8 days and 14 days greater than today then call it “8 – 14 Days”

If the NCD is greater than 14 days from today then call it “14 Days plus”

If there is no NCD then call it “blank”

thanks in advance

Iain

[#2 · Accepted solution]

try this

if(num(NCD)<num(today()),'Expired',

if(num(NCD)>=num(today()) and num(NCD)<num(today()+7),'0 – 7 Days',

if(num(NCD)>=num(num(today()+7)) and num(NCD)<num(today()+14),'8 – 14 Days',

if(num(NCD)>num(today()+14),'14 Days plus','Blank')

)))

[#3]

Thanks, this does the job however where I have an empty NCD, i.e. no date has been entered, this is returning "Expired" instead of "Blank"

Thanks

Iain