Ever had the need to categorize/sort data so you can use it for fiscal months?
This formula will categorize data based upon the fiscal week that a date field
falls into. Armed with the knowledge of which fiscal weeks fall into a fiscal
month, you can retrieve the summarized data from a view.
Date2 := @TextToTime("1/1/" + @Text(@Year(Date)));
DaysToStartDate := 7-((@Weekday(Date2))-1);
YearStartDate := @Adjust(Date2;0;0;DaysToStartDate;0;0;0);
FiscalWeek:=@If ((((Date-YearStartDate)/86400)/7) < 0 ; 52 ;
@Integer((((Date-YearStartDate)/86400)/7)) + 1);
@If ((((Date-YearStartDate)/86400)/7) < 0 ; @Text(@Year(Date)-1) +
@Text(FiscalWeek) ; @Text(@Year(Date)) + @Text(FiscalWeek))
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.