Create a line for each day off from a start date and the duration of the days off

[Jadreani]

Dear friends,

can you please help me? I need to calculate the hours of absenteism per day. The data that I can get is the following:

Name	Total Days off	Start date
A	2	16.01.2023
A	1	02.03.2023
B	3	19.12.2022
B	1	03.02.2023
C	3	01.06.2023
C	2	12.01.2023

The result expected is the following: One line for each day off that are only a workday (I already have a dimdate with a column "workday" = true or false))

Please find below the expected table: 

Name	Total Days off	Start date
A	1	16.01.2023
A	1	17.01.2023
A	1	02.03.2023
B	1	19.12.2022
B	1	20.12.2022
B	1	21.12.2022
B	1	03.02.2023
C	1	01.06.2023
C	1	02.06.2023
C	1	05.06.2023
C	1	12.01.2023
C	1	13.01.2023

I would really appreciate your help 🙂

Kind regards,

[Jadreani]

In order to create a visual per day and per team of the capacity. Thanks

[andhiii079845]

Please read what I write some hours before. It is not a easy question and you have to think about your data model. Also "In order to create a visual per day and per team of the capacity" . You have to define capacity of the team. Perhaps you show us first your underlaying data structure with some examples (best case with the internal table function, i hate it to copy data from screenshots....)

[Jadreani]

Hello, 

there is an additionnal colum to this table "Hours per day". The goal is to calculate the sum of hours of absenteism.

Name	Total Days off	Hours	Start date
A	1	7.90	16.01.2023
A	1	7.90	17.01.2023
A	1	7.90	02.03.2023
B	1	7.90	19.12.2022
B	1	7.90	20.12.2022
B	1	7.90	21.12.2022
B	1	7.90	03.02.2023
C	1	7.90	01.06.2023
C	1	7.90	02.06.2023
C	1	7.90	05.06.2023
C	1	7.90	12.01.2023
C	1	7.90	13.01.2023

[andhiii079845]

Perfect, I had the right feeling. If you have a question next time, write directly the goal of the task and do not only describe your solution (additional a solution is great!). Sometimes your solution is the wrong way and ppl invest time to help and than we go in the wrong direction. 🙂

You have to think about a data model. You can than easy calculate sum of hours of absenteism.
Create a table for the date (dimensional table) with the flag of a working day. Be aware of if a person A B C have perhaps different working days? Are the dayoff hours different from person and days? 

If you share a good data example for the underlaying data someone can help you. You can upload to one drive, google drive, dropbox your excel and share here the link.

[andhiii079845]

I have the feeling that you have to tell us more where do you want to use this new table.

Perhaps it is only a "middle part" of a more complex question. Can you please tell us more about the reason for this new table? Thank you 🙂