How can I generate a list of days?

Hello team,
I have two columns (start date, end date) used to enter employees’ holidays. Is it possibile to generate a virtual column (type list) with a list of days (date type) contained between these columns ?
Thanks

Yes you can do that somthing like TOP(LIST([StartDate],[StartDate]+1,…,[StartDate]+10),HOUR([EndDate]-[StartDate])/24)

2 Likes

Thanks Aleksi,
It’s a little uncomfortable beacuse I should insert a time period covered by the data (30 days for exemples), but it works.
Thanks!!

Another way is if you add a small table with date column and then filter that list with your StartDate and endDate.

Thanks Aleksi!

You’re welcome

Hi @Aleksi,

I have used this expression to work out the dates a person is on holiday within the date range of 13/4/2020 - 16/4/2020 which will be a total of 4 days with the dates being:

13/4/2020
14/4/2020
15/4/2020
16/4/2020

By using your expression below:

TOP(LIST([Date_From],[Date_From]+1,[Date_From]+10),hour([Date_To]-[Date_From])/24)

The answer it gives me is:

4/13/2020 , 4/14/2020 , 4/23/2020

am I doing something wrong to return the desired outcome with this expression?

Best,
Chris.

Hi @Chris_Jeal ,
with this method you must enter as many ‘[Date_From] + …’ as the number of days you want to cover with the formula. In my case, I set up to 30 days, therefore:
TOP (LIST ([Date_From],
[Date_From] +1,
[Date_From] +2,
[Date_From] +3,
[Date_From] +4,
[Date_From] +5,
[Date_From] + …,
[Date_From] +30),
HOUR ([Date_To] - [Date_From]) / 24)

In your case the correct formula is:
TOP (LIST ([Date_From],
[Date_From] +1,
[Date_From] +2,
[Date_From] +3,
[Date_From] +4),
HOUR ([Date_To] - [Date_From]) / 24)

But you need to consider integrating it if [Date_To] - [Date_From]’ can result in more than 4 days.
A little redundant method but it works

Ah, ok.
Thanks for the help.

C

Hi @sergio_f & @Aleksi,

This works for me at row level, would ther be a way for this to work for the entire table?

I have a table of holiday requests rows with from and to dates, I now have the dates in between for each request/row but could do with a full list with those dates that I can compare with a list of shift dates to show whether or not a person is on holiday or not.

Any ideas?

Hi @Chris_Jeal,
I think you may find this discussion useful, the purpose is similar to yours.

Hi guys,

I have a VC list of dates between using:

TOP (LIST ([Date_From],
[Date_From] +1,
[Date_From] +2,
[Date_From] +3,
[Date_From] +4,
[Date_From] +5,
[Date_From] + …,
[Date_From] +30),
HOUR ([Date_To] - [Date_From]) / 24)

but I wonder why this expression:

in(today(),list([dates between]))

is throwing up false when the date of today is actually in fact one in the list.

I wonder if you have any ideas?

Best,
Chris.

You tried:

in(today(),list([dates between]))

Have you tried this?

in(today(),[dates between])
3 Likes

I’ve not tried that @Steve but I will and feedback.
Thanks for the suggestion.

1 Like

worked a treat @Steve thanks again.

1 Like