Macro on open for Fiscal Year

[james]

All- any idea or recommendations for having a MACRO or something equivalent select the correct FY and correct FM based on a date? I have Fiscal Yearand Fiscal Month in the data model, however, i am stuck. Any ideas?

Below  is my MACRO for CY and selection of todays date, which works perfect for Calendar Year.

sub OnOpen
ActiveDocument.ClearAll
If ActiveDocument.Evaluate("Day(today())") = "1" and ActiveDocument.Evaluate("Month(today())") = "Jan" Then
set y=ActiveDocument.Fields("Year")
set m=ActiveDocument.Fields("Month")
y.Select ActiveDocument.Evaluate("Year(today())-1")
m.Select ">= 1" & "<=12"

else
If ActiveDocument.Evaluate("Day(today())") = "1" Then
set y=ActiveDocument.Fields("Year")
set m=ActiveDocument.Fields("Month")
y.Select ActiveDocument.Evaluate("Year(today())")
m.Select ">= 1" & "<" & ActiveDocument.Evaluate("num(month(today()))")

Else

set y=ActiveDocument.Fields("Year")
set m=ActiveDocument.Fields("Month")
y.Select ActiveDocument.Evaluate("Year(today())")
m.Select ">= 1" & "<=" & ActiveDocument.Evaluate("num(month(today()))")
end if
end if
end sub

[#2]

If(date(today())>=28,Month(addmonths(today(),1),Month(today()).

Kiran.

[james]

TRUe, but every month has different start dates...

[whiteline]

So, you can just create a table of 365 day values and two fields that represents offsets of years and months.

[james]

Yes, i have over 7 years of dates the fiscal pulls from... creating offsets? Not really sure im follwoing

[#6]

James:

For the past you need not worry since the application needs to respond from this day forward. If there is a definite logic for end dates likes, last sunday of the month etc, you can parametreize it using today, monthend and weekstart date functions.

Problem is only if there is no logic of month end and start dates.

Kiran.