Creating Date Range Values

[#1]

hi Community Members,

I written the code below

If(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')=0,'Current',

If(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=30,'0-30 Days',
If(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=60,'31-60 Days',
If(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=90,'61-90 Days',
If(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=120,'91-120 Days',
If(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=150,'121-150 Days','Over 150')))))) as ReceivablesAge

My Rquirement is

THe field should look like below

ReceivablesAge

Current

31-60 days

61-90 days

...

Over 150 days

I am getting only  over 150 value in the filed, can please you help me out

[MayilVahanan]

HI

Use like this

If(Date('06-12-1999')-Date#(txBilling.INVOICE,'MM-DD-YY')=0,'Current',

If(Date('06-12-1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=30,'0-30 Days',
If(Date('06-12-1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=60,'31-60 Days',
If(Date('06-12-1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=90,'61-90 Days',
If(Date('06-12-1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=120,'91-120 Days',
If(Date('06-12-1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=150,'121-150 Days','Over 150')))))) as ReceivablesAge

And Check Maximum and minimum date for txtBilling.Invoice value

[#3]

I am not getting the values,

Please help me out Ramasamy....how to check the min and max dates,can you write the solution in detail

[MayilVahanan]

Hi

   Use  Min(txBilling.Invoice) and Max(txtBiling.Invoice)  to know it..

Use Date insteadof Date# , if  your txtBilling.Invoice is datefield means

[#5]

Hi ramasamy,

the minimum invoice date is 2033th Year and max invoice date is 28459th Year,

and I am getting the same result, can you say where is the problem

[MayilVahanan]

Hi

     Can you post a sample file

[#7]

Hi samy,

I have send the file to your mail, please check it and reply me

Regards

Ramesh

[sivarajs]

Please check your date format and ensure Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')  giving days count

If(fabs(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')=0),'Current',
If(fabs(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=30),'0-30 Days',
If(fabs(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=60),'31-60 Days',
If(fabs(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=90),'61-90 Days',
If(fabs(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=120),'91-120 Days',
If(fabs(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=150),'121-150 Days','Over 150')))))) as ReceivablesAge