Ahmed Mostafa
New Member
- Joined
- May 15, 2024
- Messages
- 1
- Office Version
- 2013
- Platform
- Windows
Hello All,
i am working in an hotel and i have been receiving requests for groups, like he wants 20 Rooms from 11-05 till 17-05.
so i was trying to come with one equation to distribute the rooms over the dates like seeing each date will have how many rooms .
so from 11-05 till 17-05 , that mean we have 20 rooms on (11,12,13,14,15,16) May .
i have tried the sum product function but it wasn't as i hoped , the function that will give the result i want will be the Sumifs , however i failed to make the criteria dynamic
with taking in mind that the end date is not calculated as the rooms will be checked out on this date.
so i have been trying to see on each night how many room will i receive
i have been trying to manipulate using following SUMIFS
SUMIFS(C:C,A:A,"<=12-05-2024",B:B,">12-05-2024")
but i neet to make the date dynamic
i am working in an hotel and i have been receiving requests for groups, like he wants 20 Rooms from 11-05 till 17-05.
so i was trying to come with one equation to distribute the rooms over the dates like seeing each date will have how many rooms .
so from 11-05 till 17-05 , that mean we have 20 rooms on (11,12,13,14,15,16) May .
i have tried the sum product function but it wasn't as i hoped , the function that will give the result i want will be the Sumifs , however i failed to make the criteria dynamic
| Start Date | End Date | # Rooms | Date |
| 11-05-24 | 13-05-24 | 10 | 11-05-24 |
| 15-05-24 | 17-05-24 | 10 | 12-05-24 |
| 12-05-24 | 13-05-24 | 10 | 13-05-24 |
| 15-05-24 | 17-05-24 | 10 | 14-05-24 |
| 22-05-24 | 23-05-24 | 10 | 15-05-24 |
| 25-05-24 | 27-05-24 | 10 | 16-05-24 |
| 26-05-24 | 27-05-24 | 10 | 17-05-24 |
| 29-05-24 | 31-05-24 | 10 | 18-05-24 |
| 29-05-24 | 30-05-24 | 40 | 19-05-24 |
| 01-06-24 | 03-06-24 | 10 | 20-05-24 |
| 02-06-24 | 03-06-24 | 30 | 21-05-24 |
| 05-06-24 | 07-06-24 | 10 | 22-05-24 |
| 05-06-24 | 06-06-24 | 20 | 23-05-24 |
| 08-06-24 | 10-06-24 | 10 | 24-05-24 |
| 09-06-24 | 10-06-24 | 15 | 25-05-24 |
| 12-05-24 | 14-05-24 | 20 | 26-05-24 |
| 12-05-24 | 18-05-24 | 10 | 27-05-24 |
| 15-05-24 | 17-05-24 | 30 | 28-05-24 |
| 16-05-24 | 20-05-24 | 10 | 29-05-24 |
| 19-05-24 | 25-05-24 | 15 | 30-05-24 |
with taking in mind that the end date is not calculated as the rooms will be checked out on this date.
so i have been trying to see on each night how many room will i receive
i have been trying to manipulate using following SUMIFS
SUMIFS(C:C,A:A,"<=12-05-2024",B:B,">12-05-2024")
but i neet to make the date dynamic
