Extracting dates from date ranges

[Anonymous]

Hi all,

I have a data coming from our HR tool like this:

Name	Start date of absence	End date of absence
John Doe	01/10/2018	02/10/2018
Jane Doe	02/10/2018	05/10/2018

I would like to generate a new table using the information above like the following:

Name	Absence date
John Doe	01/10/2018
John Doe	02/10/2018
Jane Doe	02/10/2018
Jane Doe	03/10/2018
Jane Doe	04/10/2018
Jane Doe	05/10/2018

Do you have any thoughts on how to convert this data?

Thanks!

Ugur

[henriquesilveir]

@Anonymous hello, you can use the Unpivot table, it will turn 2 columns into 1 column, take a look in this quickly video, and learn how to turn the number of your columns your need in 1.

Please, don' forget to mark this post as solution if you got it! other people can have the same situation in the future! 

[Zubair_Muhammad · Accepted solution]

@Anonymous

In the Query Editor..... Add a custom column as follows and convert it to date format

It will give you a list of dates from start to end.

{Number.From([Start date of absence])..Number.From([End date of absence])}

Then expand the list to new rows

See file attached as well

[Uchiha85]

Hi  Zubair_Muhammad

First of all, thanks for reply on this issue, I was looking for exactly same answer and this help me a lot, thinking in this same case, do you know if there´s a way to calculate the dates in range but considering only Monday to Friday? I´m working also in calculate absences but only that ones that apply to working week.

Thanks in advanced.

[Anonymous]

how to do this on custom tables?

[Anonymous]

Ist there also a formular for doing it the other way around.

So to get a Coloumn for each day to start and end date?

[Anonymous]

Hi @Zubair_Muhammad, That worked really well! Thanks!