Fiscal Week Calculation for a View Categorization

Mindwatering Incorporated

Author: Tripp W Black

Created: 10/17/2000 at 04:40 PM


Notes Developer Tips
Formulas, Views

A friend of mine, Tracy, has some greatly improved code for getting the fiscal week in the year.

Tracy Lankford, MCSE,  
Lotus Notes Manager
I started thinking about this and came up with a much simpler way.

For just the number, you can use @Businessdays to count the number of Sundays that have elapsed.  For example...
This does return "week zero" for the first few days of the year before the first Sunday, but that's fine with me.

The next problem was turning this "week number" into an actual date.  For that, this formula works...
        @Adjust([01/01/2000];@Year(PAdate)-2000;0;@BusinessDays(@Date(@Year(PAdate);1;1);PAdate;2:3:4:5:6:7)*7-1;0;0;0 )
There is probably a slightly simpler way to do this part,

In both the above, "PAdate" is the date field I want the fiscal week of.


This document is from the Advisor via the Search Domino tips emails.
By Jeff Crossett
Buffalo, New York

This formula allows you to categorize by fiscal weeks.

Date := YourDateField ;
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))

previous page