I am working on a report, which must subtotal amounts by week.

I am trying to set up a field to calculate the week for a date in
another field. Saturday is the first day of the week. Ex. - April 24,
2007 in the 'date field' would translate to April 21, 2007 in the
'week field'.

Does anyone have a formula for this calculation? Any help would be
appreciated.

Thanks...JohnB

JohnB frug:

You could use brute force and create a table with a record for every
single day of our time and a field that says which period this week
belongs to.

For a direct calculation, try the case function:

Case(
mydate is saturday; use mydate
mydate is sunday; use the day befor;
mydate is monday; go back two days;
and so on.

There are date functions for all your needs. Good luck!
--
http://clk.ch

In article <1177757507.730757.85610 (AT) c35g2000hsg (DOT) googlegroups.com>,
JohnB <bilecky (AT) excite (DOT) com> wrote:

You can build a calculation using the built-in Filemaker functions
DayOfWeek and WeekOfYear.

DayOfWeek returns Sunday as day 1, Saturday as day 7. So you would have
to adjust for Saturday being the start of the week for your purposes.

--
For email, change <fake> to <earthlink>
Bill Collins

One field I have that is included with a date transaction line is a
WK.Num field. I use a format of yyyy.wk. I do the same with to
create a month field yyyy.mm. Then I can create/search for
appropriate records and do summaries on them as needed.
TK



On Apr 28, 5:51 am, JohnB <bile... (AT) excite (DOT) com> wrote:

On Apr 28, 5:51 am, JohnB <bile... (AT) excite (DOT) com> wrote:
Luckily FileMaker's Date fields work using days as the base unit, so
you can add and subtract days very easily.

For your Calculation you just need to work out how many days the date
is past Saturday, and then subtract that from the date.
eg.
DateWeek Calculation, Date Result, Unstored
= DateField - Case(DayName(DateField) = "Saturday", 0,
DayName(DateField) = "Sunday", 1,
DayName(DateField) = "Monday", 2,
DayName(DateField) = "Tuesday", 3,
DayName(DateField) = "Wednesday", 4,
DayName(DateField) = "Thursday", 5,
DayName(DateField) = "Friday", 6)

If DateField is April 24, 2007 then this will calculate out as:

April 24, 2007 - 3 (for Tuesday) = April 21, 2007


There are lots of other ways to achieve the same thing, including using
the DayOfWeek function, but this is probably the easiest to understand.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)