Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 2971 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

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.

 

Posted
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.

  • Like 1
Posted

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.

Posted
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?

Posted

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

Posted
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.

This topic is 2971 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.