Unique dates between 2 dates (start - end)

Hello,
I am looking for a solution/formula that will generate in a Multi-Select field all unique dates between ‘start date’ and ‘end date’.
I need that solution to generate a report with rollup fields and calculate the number of inhouse guests on specific dates, for exemple in hotel room management, or vehicles car rental.
Thanks
43

Hi @Walid_Chaker - this might work for you. If you have an end and a start date, you can calculate the in-between dates in other fields, one date per column. This requires you to define the columns ahead of time and won’t work for any date range (specifically very long ranges greater in length than the number of columns you have defined), but if you know you ranges will be less than, say, 20 days, you can build columns to suit. Here is the set up for a date range of 5 days and you can extend as you see fit:

The Day 2 formula is:

IF(DATEADD(Start, 1, 'days') < End, DATEADD(Start, 1, 'days'))

Day 3 is:

IF(DATEADD({Day 2}, 1, 'days') < End, DATEADD({Day 2}, 1, 'days'))

Then join all of your dates together into a string like this:

IF({Start}, DATETIME_FORMAT({Start}, 'YYYY-MM-DD')) & 
IF({Day 2}, ',' & DATETIME_FORMAT({Day 2}, 'YYYY-MM-DD')) &
IF({Day 3}, ',' & DATETIME_FORMAT({Day 3}, 'YYYY-MM-DD')) &
IF({Day 4}, ',' & DATETIME_FORMAT({Day 4}, 'YYYY-MM-DD')) &
IF({End}, ',' & DATETIME_FORMAT({End}, 'YYYY-MM-DD'))

In a separate calendar table, create a record for each date in the calendar:

In the first table, create a link field to the calendar table and copy your concatenated string into the link field:

This will make the link back to the calendar table and you can total the number of records by date in the calendar table

JB

1 Like

The copy & paste method will also work with a multi-select, but the multi-select will get very long over time, so I think a linked record works better.