Help

Unique dates between 2 dates (start - end)

Topic Labels: Formulas
3175 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Walid_Chaker
4 - Data Explorer
4 - Data Explorer

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

5 Replies 5

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:

02

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:

41

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

19

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

07

JB

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Cloe
5 - Automation Enthusiast
5 - Automation Enthusiast

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