Forum Discussion
Extracting dates from date ranges
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
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
6 Replies
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
Hi Zubair_Muhammad, That worked really well! Thanks!
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!
