i am certainly no expert, but i think i have a solution for you. the weekofyear function returns a number from 1-52 based on the calendar year. so on jan 1, 2009, it should return a value of 1. basd on what you said, i would actually expect your calculation to return a -12 in the case of a time sheet started on jan 1st. anyway, that is a detail to work out later. if i understand how you tried to set it up, your calculation should work fine for the dates in betwen your fiscal start week and the end of the year, and will not function properly from the 1st of the calendar year up until the start of your fiscal year. this will happen every year.
what i would recommend doing, is setting up a global date field (e.e. "start of fiscal year") into which you enter the start of your fiscal tax year (06/04/08). you will either need to update this every fiscal yea, or simply set up a calculation or a script to auto enter this each year (i could help with this too if you need it). once you have your fiscal start date entered, you will need to change the equation which calculates the week number to:
WeekOfYear(Week_Begin-start of fiscal year)
actually, you will need to set it up as:
WeekOfYear((Week_Begin+1)-start of fiscal year)
so that if you happen to start your first time sheet on 06/04/08 it will return a "1" and not a "0".
does that make sense?
i also noticed another solution in the knowledge base:
Maybe the function WeekOfYearFiscal ( date , startdate ) is usefull to you?
You would use it like:
WeekOfYearFiscal ( Date ( 1 ; 20 ; 2009 ) ; Date ( 4 ; 6 ; 2008 ) )
and that should give you the weeknumber for this week counted from week 1 of your fiscal year
much better... i didn't realize that was an option.
good job menno!
I used this function since fm pro 3. Most of the time to calculate the ISO weeknumber where the week starts on monday:
WeekOfYearFiscal ( theDate ; 2 )
With this calc you determine the weeknumber for a specific date as it is commonly used in european countries (i'm from the Netherlands) :)
Many thanks for the help
I tried copying and pasting your calculation,
WeekOfYearFiscal ( Date ( 1 ; 20 ; 2009 ) ; Date ( 4 ; 6 ; 2008 ) )
But it ended up changing every entry to week 3??
There's obviously something i'm missing here. I had a look at the knowledge base article that johnhorner supplied and it looks quite complicated to me at the moment.
I'm new to Filemaker and think i need to do some more research into how filemaker works with dates etc, as at the moment i don't really understand!
The WeekOfYearFiscal takes two parameters. The first parameter is any calendar date, and the second parameter is the starting Day. That is, Sunday through Saturday.
One way to obtain the Fiscal week is to subtract the WeekofYear of the starting date from the WeekOfYear of the ending date. As you have discovered, if you cross the year boundary, then you get a negative number. This can be resolved by using teh Mod function.
Also, if the date happens in the same week as the starting day, the result is 0 (zero) where you want it to be 1 (one). Therefore, a possible formula could be:
Mod (WeekOfYear (<Date Field> ) - WeekOfYear (Date (6; 4; 2008) ; 52) + 1
i think i may have jumped the gun in thinking the weekofyearfiscal function performed the calculation you wanted. it does not. you can read a little bit more about what it actually does at the following link:
essentially, all that the weekofyear fiscal calculation does that the weekofyear calculation does not do, is fine tune your calculation based upon whether you are using an american or iso (european) standard week (which start on sunday or monday respectively) or any other day of the week you wish to specify. it still calculates the week number from the beginning of the calendar year. since your fiscal year starts on a sunday, the weekofyear function will return the same value as the weekofyearfiscal if you set it up with a "1" (which denotes "sunday") as the second parameter ("startday").
anyway, you can still use the method i described in my previous post. you do not actually need to use the mod function as the weekofyear function still returns the correct value even if the begin date for your timesheet is in the next calendar year (or even many years later). it is essentially self mod-ing. you can use the mod function but it will be a little more complicated a there are actually 53 weeks (not full weeks but as 7x52 = 364 there will be at least 1 day every year that falls in the 53rd week and 2 days on a leap year). so depending on the startday for your fiscal year, you would need an if-then calculation to determine whether in a particular year you should be using a mod of 52 or 53 (in your case 53). but there is no need to do this as the weekofyear function will do it automatically.
so it is still pretty simple. you just need to create one global date field with your fiscal start date (e.g. "fical start date"), and you need to replace the calculation your week_current field with:
WeekOfYear((Week_Begin+1) - start of fiscal year)
hope that helps!
I've been trying out your solution but can't seem to get it to work.
To recap my problem, I'm using the default "starter solutions" Time Card template, and have been entering weekly time cards since week starting Monday 31st March 2008, which was week one of my Tax year. (Tax year started 6th April 2008)
Filemaker correctly marked this as week one. Everything has been fine until entering the first Time Card for 2009 (week starting Monday 5th January 2009) which should of been week 41. But filemaker marked this week as -12?
All I want is for Filemaker to carry on numbering the weeks correctly until the next tax year?
I've tried copying your calculation "WeekOfYear((Week_Begin+1) - start of fiscal year)" in the current week field as you mention, but filemaker displays the message "The specified field cant be found".
Now I assume this error message refers to the global fiscal start date that i need to create. So sorry to be really thick, but how do i go about setting a global fiscal start date with the default template?
Many thanks for the help so far
to try to figure this out i set up the solution i proposed in the time cards starter solution from version 10 (but i don't thiink the version should make a difference). so i am confident we can get it working for you.
first, i am a little confused. if your tax year starts on april 6th (which is a sunday in 2008), then the week starting monday, march 31st would actually have been the last fiscal week (either #52 or#53, i would have to check that) of the 2007 fiscal year. am i missing something?
in any case, it sounds like the main problem you are now having is that you have not set up the global field to contain the start date for your fiscal year. to do this you need to do the following steps:
1. go to file/manage/database
2. at the bottom of that window there is space to type in a "field name"
3. type in the name for your field which will contain the fiscal year start date (i used "start of fiscal year" but you can call it anything you like (more or less).
4. to the right of this is a pop up menu to specify the type of field.
5. select "date"
6. below where you typed in the field name you have the option to type in any comments or notes about this field (it is not required and does not affect the function of the field or appear in your layout)
7. click on the "create" button (your new field will be added to the list of fields in the window above)
8. important: click on the "options" button (or double click on the field name in the list above). you will get a dialog window to specify some options for this field.
9. click the "storage" tab.
10. click the check box for "global storage" and hit "ok".
11. click "ok" again in the main "manage database" window.
at this point you have created a global field into which you will need to type in your fiscal year start date. when you return to browse mode you will see that your new field has been added to the layout at the bottom. type your start date into this field (enter it in mm/dd/yyyy format). now that this is set up and has a value, go back and enter in the calculation (using the name of your new field in the place where i had "start of fiscal year" in the formula). if you also called your new field "start of fiscal year", then you can use the formula as is. once you do this, the correct week of year should appear. the last step is either to go into layout mode and moce the new global field somewhere where you would like to have it in your layout, or you can delete it from the layout entirely. this will not delete the field itself, only it's appearance on the layout. the date you entered will be stored there permanently until you manually change or delete it.
give it a try and let me know how it works... i hope it does the trick!
You are a star! The calculation works great.
For some reason I couldn't get it to work on my existing file, so i created a new Time Card template and imported all my records across.
And now all weeks are numbered correctly!!
Many thanks for taking the time to help me out.
glad to have been able to help out.... i wish i knew why it wouldn't work in your existing file? but at least it's working now!
the only disclaimer i would make to my solution is that you might need to double check that it is working properly under at least 2 specific conditions i can think of:
1. the first and last day of your fiscal year (you may need to modify the calculation with a "+1" or "-1" somewhere)
2. and check it again after more than a full fiscal year has elapsed since your global "start of fiscal year" date to make sure that it is correctly "mod"-ing itself.
both of those seemed to work on my test solution (in fm10) but i just didn't want you to think i was infalible... only a star!
all the best,