currently working on creating a report in crystal

the current date format is backward eg "20140601"

I need to add on 1 year to the above making it "20150601"

I ended up using the below formula to convert the number to a date

if {SSJOBH.SERSDJ} = 999999999
    then Date(0,0,0)
    else
       (
        NumberVar yyyy := Truncate ({SSJOBH.SERSDJ}/10000); //extract year
        NumberVar mm := Truncate(({SSJOBH.SERSDJ} - (yyyy * 10000))/100); //extract month
        NumberVar dd := {SSJOBH.SERSDJ} - (yyyy * 10000) - (mm * 100); //extract day
        Date(yyyy,mm,dd)
       )

then once I did this - I was able to add on 365 days.

which resulted in it being "01/06/2015"

But to upload this info into system - I need to get it back to the number format - being "20150601"

FYI - this is for a lot of different data with multiple dates to change

Hope this makes sense

any ideas thanks

A faster way of doing it, could be something different.

This formula is converting it first to a date format, adds 365 days to it, and then converts it back to a text again. See if it works for you.

totext(date(tonumber(left({SSJOBH.SERSDJ},4)),
     tonumber(mid({SSJOBH.SERSDJ},5,2)),
     tonumber(right({SSJOBH.SERSDJ},2))
)+365,'yyyyMMdd')

what format is the SSJOBH.SERSDJ field?

try this

totext(date(tonumber(left(totext({SSJOBH.SERSDJ},0,''),4)),
     tonumber(mid(totext({SSJOBH.SERSDJ},0,''),5,2)),
     tonumber(right(totext({SSJOBH.SERSDJ},0,''),2))
)+365,'yyyyMMdd')

Edited by Gurbs - 24 Jun 2014 at 3:48am