Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
  
Matching DATE fields - by week / certain week, month, quarter, year
Very often companies use period (term) - # of WEEK (from 1 to 52 in a year).

Now possible to filter (match) records on date filed by this/last/next month/quarter/year (+ =date and days in future/past).
But this/last/next are only 3 cases (for months in a year - 12 values).

Users must have possibility to set matching
- # of week (+year)
- # of month (+year)
- # of quarter (+year)
ID
210
Category
Customization
Author

Anatoliy Zachynskiy
Date Created
5/3/2008 7:23:42 AM
Date Updated
5/5/2008 2:32:19 AM
Status
New Idea
Score
10
Promoted By
Anatoliy Zachynskiy
Comments
Kirill Bondar  Staff  5/4/2008 4:51:42 AM
We do not operate with weeks since rules for weeks vary from country to country. In the US first day of week is Sunday, in Europe is Monday. This affects the calculation of the week beginning and end.

The same problem appears with calculating week number: US defines first week as the week containing January, 1st. European standard defines the first week as the one with the majority of days (four or more) falling in the new year.

As there is no general rule for handling weeks, you may "emulate" week logic specific to your country using FirstDayOfPeriod/LastDayOfPeriod functions.
Anatoliy Zachynskiy 5/5/2008 2:32:49 AM
Is it right formula for receiving # of week ? (It works, but maybe better other?)

Floor((ToDays([DATE_FIELD] - FirstDayOfYear([DATE_FIELD])) + 1) / 7) + 1

Kirill Bondar  Staff  5/6/2008 3:56:25 AM
This formula will calculate week number according to US rules:

Mod((FirstDayOfPeriod([Date Column], Days(7), Date(2000, 12, 31)) - Date(2000, 12, 31)) / Days(7), 52)

Magic Date(2000, 12, 31) specifies first day of week - Sunday in this case.

Feedback
 
Back to Search Results