9 Replies Latest reply on Dec 29, 2016 3:54 PM by Bagiyaraj Natarajan

# How to calculate WorkWeek based Week Starting Day and Time?

Hi, My workweek starts at Wednesday 7 PM and ends at Wednesday 6:59 PM the following week. can someone help me how to calculate the workweek from the available dates ? I can only change the "Starting Day" of the workweek from "Date Properties". How do I input the starting time for the day?. I am using 9.3 version.

• ###### 1. Re: How to calculate WorkWeek based Week Starting Day and Time?

Hi Bagiyaraj,

Find my approach below and stored in attached workbook version 9.3

• ###### 2. Re: How to calculate WorkWeek based Week Starting Day and Time?

Hi Norbert, thank you for your suggestion.

Unfortunately this does not help me.

I have the DATE column. What I am trying to do is to calculate the WORKWEEK# based on the DATE column. The Workweek Should start at Wednesday 7 PM and ends at following Wednesday 06:59:59 PM.

• ###### 3. Re: How to calculate WorkWeek based Week Starting Day and Time?

Hi Bagiyaraj,

Can you be more specific? Is the first week, the one containing the first Wednesday of the year etc.? If it aligns with regular calendar e.g. 1st week of year based on standard Gregorian calendar but instead of Sunday midnight, it starts at Wednesday 7 PM, you can just offset your date like Norbert suggested (or in the opposite direction) and use the week number of that date and it should work.

E.g. 1st week of 2017 starts with Sunday, January 1st. If you ask Tableau for a week number you would get 1.

What Norbert's calculation does is that it moves the date to Wednesday the week before and have Tableau give 52nd week of 2016.

When your date it is January 3rd 2017, offsetting would result in Tableau returning the week number for January 1st is and will result in 1st week of 2017 which I think is what you expect.

Do the numbers don't match what you expected?

Thanks,

Bora

• ###### 4. Re: How to calculate WorkWeek based Week Starting Day and Time?

Hi Bora,

Thanks for the reply. I think this works. I made a mistake with my calculation.

Hi Bora Beran and @Norbert Maijoor,

Could you tell me how did you arrive the number "-113" for "adjusted day"? If My work week start time has to be changed to Thursday 7 PM instead of Wednesday 7 PM, What should this number be?

Thanks,

Bagi

• ###### 5. Re: How to calculate WorkWeek based Week Starting Day and Time?

Hi Bora,

Thanks for the reply. I think this works. I made a mistake with my calculation.

Hi Bora Beran and @Norbert Maijoor,

Could you tell me how did you arrive the number "-113" for "adjusted day"? If My work week start time has to be changed to Thursday 7 PM instead of Wednesday 7 PM, What should this number be?

Taking this year 2016 as an example, The last workweek of 2016 will end at 12/29/2016 06:59:59 PM. The new WW01 for 2017 should start at 12/29/2016 07:00:00 PM and the week number calculation should start from here.. Hope this clarifies. Can you help me to get the equivalent "interval" for this requirement?

Thanks,

Bagi

• ###### 6. Re: How to calculate WorkWeek based Week Starting Day and Time?

would take you from Thursday 7 PM to next week Sunday midnight which is the Week 1 of 2017 (if your start of week setting says Sunday in Tableau) so it would make it look like 12/29/2016 07:00:00 PM is Week 1 of 2017.  12/29/2016 06:59 PM would become Saturday 11:59 PM and appear as Week 53 of 2016.

Note that what this does is to swap out Thursday 7 PM with next week's Sunday midnight (add another 24 to make it 77 if you want to push it to Monday) then Tableau decides what week that Monday or Sunday is and displays that. So it is still tied to the week number of the adjusted date in Gregorian calendar.

If Gregorian calendar definition of week boundaries doesn't meet your requirements then it may not work.

Thanks,

Bora

• ###### 7. Re: How to calculate WorkWeek based Week Starting Day and Time?

Hi Bora,

Thank you!

It works this time. But my current workweek should be WW52 (Starts on 12/22/2016 07:00:00 PM and  Ends on 12/29/2017 06:59:59 PM) . My current calculation takes one week ahead. Currently its calculating the Workweek as WW53 which is one week ahead of current WW (WW52). Any suggestions on how to make it as WW52?.

• ###### 8. Re: How to calculate WorkWeek based Week Starting Day and Time?

In Gregorian calendar there is a 53rd week in 2016.

Is there any special logic about your calendar other than just the 2.5 day shift that would make 2016 to have 52 weeks?

• ###### 9. Re: How to calculate WorkWeek based Week Starting Day and Time?

Hi Bora,

Thank you!

There is no other special logic other than just the 2.5 day shift that would make 2016 to have 52 weeks.