Date formula to first of next month

[Robin99]

Hi,

CRv10 on Enterprise
Data source: Encore/Impact 6.0 (ODBC)
Field Name: {ARINVOICE.InvoiceDate}
Field type: Date

Background:
I'm writing a "welcome letter" (form letter) to send to our customers who buy our warranty support services.
The warranty start date is always the 1st day of the following month from the invoice date of purchase.

In other words, if their invoice date falls between Jan 1st & the 31st, their support start date is Feb 1.
If their invoice date is between Feb 1st & the 28th (or 29th in a leap year) their support start date would be March 1st and so on.

Desired Output:
I'd like to write a formula(?) that looks at the invoice date {ARINVOICE.InvoiceDate} and returns a value that is the 1st of the next month. Preferably in this format:
01-Mar-06

Any advice?

 

[damonkimble]

A combination of dateserial and dateadd should do the trick. You're taking the year and month of the given date plus one month and 1 for the day value.

cheers,
damon

dateserial(year(dateadd('m', 1, {table.datefield}))
            ,month(dateadd('m', 1, {table.datefield}))
            ,1)

 

[lbass]

Or you could use:

dateserial(year({table.purchdate}), month({table.purchdate})+1,01)

You would then format the formula by going to format field->date to get the desired format.

-LB

 

[damonkimble]

Ah yes, LB is correct too. I forgot about the relative functionality of dateserial.

damon

 

[Robin99]

Damon & LB,

Both solutions worked perfectly - Thanks to you both for the fast response!

- Robin