Forum Discussion
Calculate average rate on days between dates
Resident RockstarHi, add custom column {Number.From([Dates from])..Number.From([Dates to]) }
Expand the List, turn to date
Here is the article that explains different scenarios
https://www.linkedin.com/pulse/hr-reporting-generating-records-between-start-end-values-dontsova/?trackingId=bTPD6jERQXW4cwFZg%2FhJ0A%3D%3D
Thanks very much! olgad Two quick questions:
1) Would your solution work for multiple overlapping date ranges in the first table, it's difficult to tell from your example. For example:
ID | Nightly rate | Dates from | Dates to | Nights |
1 | 100.00 | 01/01/2023 | 10/01/2023 | 9 |
2 | 150.00 | 12/01/2023 | 18/01/2022 | 6 |
3 | 300.00 | 02/01/2023 | 03/01/2023 | 1 |
This is becasue we need to be able to average the nightly rates on specific dates.
2) These are check-in and check-out dates for stays in hotels. So therefore the rate does not apply to the last day in the range. e.g. ID 1 from the above example needs to look like this in the Output table:
| Date | Rate | ID |
| 01/01/2023 | 100 | 1 |
| 02/01/2023 | 100 | 1 |
| 03/01/2023 | 100 | 1 |
| 04/01/2023 | 100 | 1 |
| 05/01/2023 | 100 | 1 |
| 06/01/2023 | 100 | 1 |
| 07/01/2023 | 100 | 1 |
| 08/01/2023 | 100 | 1 |
| 09/01/2023 | 100 | 1 |
ie. the last date in the output table needs to be the 9th, not 10th, as rate does not apply on the check out date.
Thanks for your help!