Date 1

[slickp]

Using crystal 10, i am trying to create a formula that will give me the final day of the month, 3 months back from the currentdate month. So far i have this :

if Month (CurrentDate) = 1 then date ((Year (CurrentDate)-1), 09, 30) else
if Month (CurrentDate) = 2 then date ((Year (CurrentDate)-1), 10, 31) else
if Month (CurrentDate) = 3 then date ((Year (CurrentDate)-1), 11, 30) else
if Month (CurrentDate) = 4 then date ((Year (CurrentDate)-1), 12, 31) else
if Month (CurrentDate) = 5 then date (Year (CurrentDate), 01, 31) else
if Month (CurrentDate) = 6 then date (Year (CurrentDate), 02, 28) else
if Month (CurrentDate) = 7 then date (Year (CurrentDate), 03, 31) else
if Month (CurrentDate) = 8 then date (Year (CurrentDate), 04, 30) else
if Month (CurrentDate) = 9 then date (Year (CurrentDate), 05, 31) else
if Month (CurrentDate) = 10 then date (Year (CurrentDate), 06, 30) else
if Month (CurrentDate) = 11 then date (Year (CurrentDate), 07, 31) else
if Month (CurrentDate) = 12 then date (Year (CurrentDate), 08, 31)

Which is great, however does anyone have any ideas of how i can cater for a leap year, so that when the month is june the formula will return the date 29th Feb when it is a leap year?

Cheers

 

[Bloke152]

i cant think of an easy way to get the last day of that month but you could go backwards. so for your example, you would want the formula to create 1st march minus 1

eg
to get last day in Feb
date((Year(CurrentDate)-1), 03, 01) -1

 

[slickp]

Good idea, cheers for that mate

 

[synapsevampire]

Try:

dateserial(year(currentdate),month(currentdate)-3,1)-1

-k

 

[synapsevampire]

You only need the one formula above, not the series of IFs.

-k