How can I generate a list of days?

[sergio_f]

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

[Aleksi]

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

[Chris_Jeal]

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.

[sergio_f]

Chris_Jeal:

Date_From

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

[Chris_Jeal]

Ah, ok.
Thanks for the help.

C

[Chris_Jeal]

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?

[sergio_f]

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

Automatically create parent and child records one click Tips & Tricks 💡

I was running into a problem where I wanted my users to be able to add multiple days off for employees by just selecting a range of dates. This meant that I had to create multiple records from one form. I was able to accomplish this by using three tables. Table 1 (just updates) - is a single record form that gets updated, but no new records are added. Table 2 (just updates)- has a bunch of formulas in it that look back at table 1 to figure out how many records to create. My action button up…

[Oscar_Rossini]

can not input Dates in or out … ???

[Oscar_Rossini]

I can not input Date_From, Date_to in the form
???
how do I solve it ?

[Oscar_Rossini]

please, where must to put this formula, thanks

[sergio_f]

Aleksi:

TOP(LIST([StartDate],[StartDate]+1,…,[StartDate]+10),HOUR([EndDate]-[StartDate])/24)

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!!

[Aleksi]

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

[sergio_f]

Thanks Aleksi!

[Oscar_Rossini]

hi
please how can do it?

[Aleksi]

You’re welcome

[Chris_Jeal]

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.

[Steve]

You tried:

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

Have you tried this?

in(today(),[dates between])

[Chris_Jeal]

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

[Chris_Jeal]

worked a treat @Steve thanks again.

[Vishnu_Tiwari]

I Have A problem i Have 16 Row data But Show only 11 row Why ?