change date format and do calculation

[christoff]

Hi,

It's been so long since I've had to do any maintenance on my database that I'm having trouble remembering how to do this, and possibly never even knew how to do this.

I want to change a date from dd/mm/yyyy to mm/yyyy format for a PeriodEnding field via a script. I know how to do this as far as in the field in the layout but I then have to export this PeriodEnding field to an excel and therefore I need the date to appear as mm/yyyy in the excel and not revert to the original dd/mm/yyyy.

I also need to calculate in a script, the PeriodBeginning field to be 3 months before the PeriodEnding field. eg if the PeriodEnding field is 06/2011, I need the PeriodBeginning field to be set as 04/2011. (what would have been 1/04/2011 in the dd/mm/yyyy format).

hope you can help me.

cheers

christoff

[christoff]

OK, after having a few light ales and trying a few different approaches I discovered something that works.

If I've set the variable of the date I want to change as $DateEnding then...

set field:

Month ( $DateEnding ) & "/" & Year ( $DateEnding )

seems to do the job...but now i need help with part 2, the calculation of the PeriodBeginning field...

[comment]

You don't need scripts or variables for this. Define two calculation fields, both with a Text result, one =

Month ( PeriodEnding ) & "/" & Year ( PeriodEnding )

and the other =

Let ( 

d = Date ( Month ( PeriodEnding ) -3 ; 1 ; Year ( PeriodEnding ) )

;

Month ( d ) & "/" & Year ( d )

)

Export the two calculation fields and leave your original data as is.

[christoff]

Awesome, thanks Michael. Worked like a charm!

I had no idea you could do that.

I did -2 instead of -3 because i needed it to reflect apr - jun rather than mar - june.

[christoff]

Oh sorry, I just have 1 more question. The formatting is appearing as 6/2011 instead of 06/2011, I found a solution but it's a bit long winded...

If (Month( PeriodEnding) < 10; "0" & Month ( PeriodEnding ) & "/" & Year ( PeriodEnding ) ; Month ( PeriodEnding ) & "/" & Year ( PeriodEnding ))

thanks

[comment]

Try =

SerialIncrement ( "00" ; Month ( PeriodEnding ) ) & "/" & Year ( PeriodEnding )

[christoff]

Ah ok, I knew there'd be an easier way, thanks again!