4 Replies Latest reply: Nov 5, 2012 10:51 AM by Gerhard Laubscher

# Calculate next Cycle Date

Hi there,

I currently have the following expression in my Load:

if(day(DATE(DATE#( [Posted Date], 'DD-MMM-YY')))<11,month(DATE(DATE#( [Posted Date], 'DD-MMM-YY'))),month(date#(month(date(DATE#([Posted Date] ,'DD-MMM-YY')))+1,'M'))) as [Posted Cycle],

What this does is look at a transaction date (Posted Date), and then determines what the Statement Cycle Month will be (Posted Cycle) for teh transaction.

If the Transaction happened before the 11th of the month, the Cycle Month will be the same month (e.g.7 November, Cycle Month is November).

If the Transaction happened after the 10th of the month, the Cycle Month will be the nextmonth (e.g.11 November, Cycle Month is December).

We cycle on teh 10th of every month - I want to change the expression to give me the cycle DATE, not just cycle month.

So in the above example it must give me 2012/11/10 (1st example), or 2012/12/10 (2nd example).

Gerhard

• ###### Re: Calculate next Cycle Date

Maybe like this?

=Date(monthstart([Posted Date]-10,1)+9)

• ###### Re: Calculate next Cycle Date

That doesn't seem to work, I get null values.

Could you maybe incorporate that into my full expression, including the date formats and the Else Condition? I'm doing something wrong.

Thanks.

• ###### Re: Calculate next Cycle Date

Ok, if your Posted Date is just a string, you'll need to interprete it:

=Date(monthstart( DATE#( [Posted Date], 'DD-MMM-YY') -10,1)+9,'YYYY/MM/DD')

• ###### Re: Calculate next Cycle Date

It's Home Time here - I will check this tomorrow morning and respond.

Thanks for the help so far.