Hi,  I'm importing records and have a date field that i need to use to set my Fiscal year, month, week based on the fiscal year starting February 1st and ending January 31st.

Any ideas or sample i might be able to take a look.

Thanks in advance.

 

3 hours ago, Nestor said:

i need to use to set my Fiscal year, month, week based on the fiscal year starting February 1st

You can calculate fiscal year as =

Year ( Datefield ) - ( Month ( Datefield ) < 2 )

and fiscal month as =

Mod ( Month ( Datefield ) - 2 ; 12 ) + 1

We need a better definition of "fiscal week" before we can suggest how to calculate it. See the help on WeekOfYear() and WeekOfYearFiscal() functions.

Thanks for the info, that worked for my year/month.  My week starts Sunday to Saturday.  This year my Week one was 1/31/2016 and goes all the way to 1/28/2017 (wk 52). 

 

hope this helps.

21 minutes ago, Nestor said:

This year my Week one was 1/31/2016

That's not a good example. You should take the case of year 2014, where February 1 is a Saturday. The question then becomes this:  is Sunday, February 2, 2014 the first day of the second week? IOW, do fractions of weeks occurring at the beginning or end of the year count as full weeks?

In the sample above February 2, 2014 (Sunday) would be week 1.  So no fractions of weeks do not count.

 

5 minutes ago, Nestor said:

So no fractions of weeks do not count.

Are you aware of the contradiction between this and your first example?

I am. writing about what i really needed helped me out. 

So what is the actual rule you want to go by?

So the rule would be.  Week starts on a Sunday end on Saturday regardless of fractions of weeks.  January is part of the previous fiscal calendar.

does that make sense.  sorry for confusing you

1 minute ago, Nestor said:

does that make sense. 

I am afraid not.

Are you doing this for a client? If so, they should provide the answer based on their business rules. This is not a trivial matter and you should not have to make up the answer yourself.

In general, weeks run independently of year/month boundaries - which is why it's not possible to have a simple, one-size-fits-all answer. The first week will almost always (6 times out of 7) be divided between January and February. The two most common approaches are:

1. The first week of the year is the week that contains the first day of the year (this is the method used by Filemaker's WeekOfYear function);
2. The first week of the year is the first week that contains four or more days of that year (this is the method used by Filemaker's WeekOfYearFiscal function).

Note that in the first method, different days of the same calendar week can have different week numbers; OTOH, in the second method, the first day of the year (actually, up to three days at the beginning of the year) can be assigned a week number in the previous year.