TeamDesk Knowledge Base & Support

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.

ID
456

Category
Setup

Author

Kirill Bondar Staff

Date Created
9/22/2011 9:52:14 AM

Date Updated
2/23/2020 8:43:08 PM

Status
New Idea

Score
40

Promoted By

	戴晓		David Rister		Martin Odendaal
	Kirill Bondar Staff

Comments

	beo 9/22/2011 11:47:23 AM I propose to take into account the type of calendar 4-5-4. http://www.nrf.com/modules.php?name=Pages&sp_id=391
	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!: https://en.wikipedia.org/wiki/Fiscal_year 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: https://www.teamdesk.net/blog/whats-new/week-numbering-functions/
	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
	David Rister 3/8/2018 12:27:15 PM I have an excel worksheet that I am using, there away to continue to use the same worksheet storing it on teamdesk
	戴晓 2/23/2020 8:43:08 PM Good idea. In China, May 1~7 and October 1~7 are national holidays, where normal Workdays function is not accurate.

Feedback

Back to Search Results