Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
Support for weeks, workweeks and weekends in filters and formulas
For a long time we avoided the term "week" in formulas. There were couple of problems:

First day of week rule vary from country to country and we should be able to determine what rules is applicable to your application. While language/locale is a part of user settings we did not want to use it as query results can vary from user to user. For example, would we rely on user-specific settings "this week" filter could return the set of records for 09/18/11 to 09/24/11 for users with English (US) locale, while users with English (UK) locale will receive the set for 09/19/11 to 09/25/11.

While workaround using First/LastDayOfPeriod() is already available with introduction of application-wide locale settings we'll be able to provide "native" support for weeks in formulas and filters using First/LastDayOfWeek() functions; this will cover most of the needs. For data-driven location-aware week calculation you can still use First/LastDayOfPeriod() functions.


Another related extension is to add workweek and weekend settings to the application to provide support for working days and weekends calculations. While in most countries weekends are Saturday and Sunday, it's not always the case. While this solution is not extensive because of holidays varying from country to country (and in some cases, from region to region) it could significantly simplify calculations of working days/weekends between two dates.

Kirill Bondar  Staff 
Date Created
9/22/2011 9:52:14 AM
Date Updated
6/6/2016 9:59:44 AM
New Idea
Related articles
Promoted By
Martin OdendaalKirill Bondar  Staff 
beo 9/22/2011 11:47:23 AM
I propose to take into account the type of calendar 4-5-4.
Kirill Bondar  Staff  9/22/2011 1:46:18 PM
For week numbering We would rather stick to ISO 8601 as it is interoperable with other software such as HTML5 capable browsers
basenine 6/6/2016 8:16:41 AM
Hello Kirill,

I know this is an old post but I have a question along a similar line. Would it be possible to put in an array at the Regional settings to help with Financial Years (similar to what you have done with the Weekend array). As with the above, not all countries respect the same financial year. Australia is July 1st to June 30th for example whereas UK is 1st of April to 31st of March and USA is 1st of January to 31st of Dec. See here for longer list!:

I have a formula:

If(Month([Date Created.])>6,Left(ToText(Year([Date Created.])),4)&"/"&Left(ToText(Year([Date Created.])+1),4),Month([Date Created.])<7,Left(ToText(Year([Date Created.])-1),4)&"/"&Left(ToText(Year([Date Created.])),4))

Which calculates 1st July to 30 June but it would be much more practical to have it set from variable in the regional settings

Slava Shinderov  Staff  6/6/2016 8:22:41 AM
@All recently, we've added new functions to allow you easily calculate week number:
basenine 6/6/2016 8:24:31 AM
Hello Slava,

I don't think you've read my comment in it's entirety!

I'm specifically asking about Financial Year array - like the Weekend array you have in regional settings
Kirill Bondar  Staff  6/6/2016 9:31:33 AM
@basenine: Except for exotic rules in Iran or Nepal Financial Year - if not matches calendar year - starts either 6 or 3 month earlier or 3 months later. To get FY you can get CY of the date offset by financial year bias. (-6 for Australia)

Year(AdjustMonth([Date], -6))

Does it really deserve separate setting?
basenine 6/6/2016 9:59:44 AM
i guess that works too

I was showing it to represent to my client 2014-2015 (as an example)... Not just the year

In your calc, say 20/8/2015 or 31/2/2016...all results will be 2015. I'd then need to represent
That in another formula to say FY 2015-2016

That way I can use it in the new Ask the User and a simple list of values (that are clear to the user) are displayed. If I just had the resulting year...it still doesn't say which part of the year

Back to Search Results