Unique dates between 2 dates (start - end)

[Walid_Chaker]

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

[JonathanBowen]

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

[JonathanBowen]

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.

[Janet_Shivell]

HI Johnathan,

I am looking to do something similar but still having difficulty. I have a contract start date and end date MM/DD/YY. I am looking to create a formula to list all the months YYYY-MM into one column of that contract.

For example Start Date 12/11/20 and End date is 6/30/21. Then the next column would list. 2020-12, 2021-01, 2021-02, 2021-03, 2021-04, 2021-05, 2020-06

From there I want to that the full value of the contract say it is $14,000 and divide it by the number of months for a monthly amount, in this case $2,000 each month.

Then I would roll this up to another table called accrual and have the first column be Month, the next would be Monthly Accrual

Are you able to help me with these two formulas?

Thanks,
Janet

[ATNewb]

Is there any automation that would work to automatically copy these values over to the Calendar field?

[Cloe]

yes but, you must have the same formatting. For the rest is an ordinary automatization