Year Start Formula

[Johnsf]

Crystal Enterprise: 8.5
OS: Windows XP

I need to set the start of the Financial year to the day after the last Friday of November i.e. the start of the financial year this time is 27/Nov/2004

The below formula that sets the start of the year to the day after the last Friday of November worked fine last year but this year its started throwing this error: A month number must be between 1 and 12

if dayofweek(Dateadd("m", -month({?Run Date})-1, date(Year({?Run Date}),month({?Run Date})+1, 01)-1)) <> 6 then
Dateadd("m", -month({?Run Date})-1, date(Year({?Run Date}),month({?Run Date})+1, 01)-1)-dayofweek(Dateadd("m", -month({?Run Date})-1, date(Year({?Run Date}),month({?Run Date})+1, 01))) else
Dateadd("m", -month({?Run Date})-1, date(Year({?Run Date}),month({?Run Date})+1, 01)-1)-dayofweek(Dateadd("m", -month({?Run Date})-1, date(Year({?Run Date}),month({?Run Date})+1, 01)))+7

Can someone please tell me whats wrong with it and how it can be fixed? It needs to return 27/Nov/2004

Cheers,

JSF

 

[Johnsf]

Oh and {?Run Date} = Today

 

[Johnsf]

Any one have any idea how to fix this problem?

Cheers,

JSF

 

[vidru]

This is adapted from a formula from Ken Hamady's site:

If (Month({?Run Date}) < 11) Or (Month({?Run Date}) = 11 And Day({?Run Date}) < Day(CurrentYear)) Then
(Date(Year({?Run Date}) - 1, 11, 2) - DayOfWeek(Date(Year({?Run Date}) - 1, 11, 2)) + 6) + 22
Else
(Date(Year({?Run Date}), 11, 2) - DayOfWeek(Date(Year({?Run Date}), 11, 2)) + 6) + 22

-dave

 

[vidru]

Missed a variable declaration when I posted that... past this at the beginning of the previous post:

DateVar CurrentYear := (Date(Year({?Run Date}), 11, 2) - DayOfWeek(Date(Year({?Run Date}), 11, 2)) + 6) + 22;

-dave

 

[Johnsf]

Thanks Vidru... It works like a dream!!!