Transform data with a start and finish timestamp to days per week

Your data looks like this and you need to report how many days an employee has worked.
time_dataset
So if ‘person 1’ starts at the first of January you would expect expect according to the calendar (2015) that this person worked 2 days in the first week of January.
time_cal

Note: This example only works with a fixed date range for the columns which will be unpivoted, these columns will be created at step 3.

  1. Create some flexible properties.
    time_properties
  2. Add the start week property to your data table and save it as ‘start date‘.
  3. Add for every week in the year a column and increase the weeks like w0, w1, w2, w.. This formula generates a start week for every week in the year by increasing the start week.
    For column w0 the formula will be: For column w1 use the same formula but change the 0 to a 1.

  4. Add for every week in the year a column with the corresponding week number 0, 1, 2, .. This formula will calculate the number of days worked per week.
    For column 0 the formula will be: For column 1 use the same formula but change every w0 to w1.

The data table now looks like this:
time_table1

  1. Unpivot the data table to a new data table and add a column to calculate the start week based on the category. See column date in the screenshot below.

    time_unpivot

Leave a Reply

Your email address will not be published. Required fields are marked *

22 − 20 =