Spreadsheet: 741801d1627356117-excel-room-booking-sheet-using-sumproduct-and-conditional-formatting-sv-booking-sheet-27-july-2021
Sheet: Room Reservation

Room Type - for calendar | Room No - only for calendar |  | Reason for Trip | Country of Residence | Booking Status? |  |  |  | Guest Name | Check In | Check Out | No of nights
 | 2 |  |  |  |  |  |  |  | Test 1 | 2021-07-08 00:00:00 | 2021-07-12 00:00:00 |  |  | In K:10, |  | Try changing the check in date to |  |  | 2021-07-06 00:00:00
 | 3 |  |  |  |  |  |  |  | Test 2 | 2021-07-08 00:00:00 | 2021-07-12 00:00:00 |  |  | In K:11, |  | Try changing the check in date to |  |  | 2021-07-05 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 1 | 2021-05-21 00:00:00 | 2021-06-02 00:00:00
Standard | 1 |  |  |  |  |  |  |  | Guest 2 | 2021-06-04 00:00:00 | 2021-06-12 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 3 | 2021-06-04 00:00:00 | 2021-06-10 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 4 | 2021-06-07 00:00:00 | 2021-06-11 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 5 | 2021-06-16 00:00:00 | 2021-06-23 00:00:00
Standard | 1 |  |  |  |  |  |  |  | Guest 6 | 2021-06-23 00:00:00 | 2021-06-27 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 7 | 2021-06-24 00:00:00 | 2021-07-01 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 8 | 2021-06-27 00:00:00 | 2021-07-02 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 9 | 2021-06-27 00:00:00 | 2021-07-01 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 10 | 2021-07-01 00:00:00 | 2021-07-05 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 11 | 2021-07-03 00:00:00 | 2021-07-07 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 12 | 2021-07-04 00:00:00 | 2021-07-07 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 13 | 2021-07-15 00:00:00 | 2021-07-22 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 14 | 2021-07-15 00:00:00 | 2021-07-23 00:00:00
Standard | 1 |  |  |  |  |  |  |  | Guest 15 | 2021-07-17 00:00:00 | 2021-07-24 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 16 | 2021-07-20 00:00:00 | 2021-07-25 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 17 | 2021-07-23 00:00:00 | 2021-07-29 00:00:00
Standard | 1 |  |  |  |  |  |  |  | Guest 18 | 2021-07-24 00:00:00 | 2021-07-30 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 19 | 2021-07-27 00:00:00 | 2021-08-01 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 20 | 2021-07-29 00:00:00 | 2021-08-02 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 21 | 2021-07-29 00:00:00 | 2021-08-05 00:00:00
Standard | 5 |  |  |  |  |  |  |  | Guest 22 | 2021-07-30 00:00:00 | 2021-08-05 00:00:00
Standard | 1 |  |  |  |  |  |  |  | Guest 23 | 2021-07-31 00:00:00 | 2021-08-05 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 24 | 2021-08-02 00:00:00 | 2021-08-10 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 25 | 2021-08-03 00:00:00 | 2021-08-10 00:00:00
Standard | 1 |  |  |  |  |  |  |  | Guest 26 | 2021-08-05 00:00:00 | 2021-08-10 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 27 | 2021-08-05 00:00:00 | 2021-08-09 00:00:00
Standard | 5 |  |  |  |  |  |  |  | Guest 28 | 2021-08-05 00:00:00 | 2021-08-16 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 29 | 2021-08-09 00:00:00 | 2021-08-13 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 30 | 2021-08-10 00:00:00 | 2021-08-13 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 31 | 2021-08-12 00:00:00 | 2021-08-15 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 32 | 2021-08-13 00:00:00 | 2021-08-19 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 33 | 2021-08-14 00:00:00 | 2021-08-17 00:00:00
Standard | 1 |  |  |  |  |  |  |  | Guest 34 | 2021-08-15 00:00:00 | 2021-08-26 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 35 | 2021-08-15 00:00:00 | 2021-08-18 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 36 | 2021-08-17 00:00:00 | 2021-08-20 00:00:00
Standard | 5 |  |  |  |  |  |  |  | Guest 37 | 2021-08-18 00:00:00 | 2021-08-26 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 38 | 2021-08-19 00:00:00 | 2021-08-30 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 39 | 2021-08-19 00:00:00 | 2021-08-26 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 40 | 2021-08-20 00:00:00 | 2021-08-23 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 41 | 2021-08-24 00:00:00 | 2021-08-27 00:00:00
Standard | 1 |  |  |  |  |  |  |  | Guest 42 | 2021-08-26 00:00:00 | 2021-08-29 00:00:00
Standard | 5 |  |  |  |  |  |  |  | Guest 43 | 2021-08-26 00:00:00 | 2021-08-31 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 44 | 2021-08-26 00:00:00 | 2021-09-03 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 45 | 2021-08-27 00:00:00 | 2021-09-01 00:00:00
Standard | 1 |  |  |  |  |  |  |  | Guest 46 | 2021-08-29 00:00:00 | 2021-09-12 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 47 | 2021-08-30 00:00:00 | 2021-09-02 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 48 | 2021-09-02 00:00:00 | 2021-09-07 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 49 | 2021-09-02 00:00:00 | 2021-09-07 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 50 | 2021-09-03 00:00:00 | 2021-09-11 00:00:00
Standard | 2 |  |  |  |  |  |  |  | [NAME] 51 | 2021-09-08 00:00:00 | 2021-09-11 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 52 | 2021-09-09 00:00:00 | 2021-09-14 00:00:00
Standard | 1 |  |  |  |  |  |  |  | Guest 53 | 2021-09-15 00:00:00 | 2021-09-19 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 54 | 2021-09-15 00:00:00 | 2021-09-19 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 55 | 2021-09-15 00:00:00 | 2021-09-19 00:00:00
Standard | 4 |  |  |  |  |  |  |  | [NAME] 56 | 2021-09-15 00:00:00 | 2021-09-19 00:00:00
Standard | 1 |  |  |  |  |  |  |  | Guest 57 | 2021-09-19 00:00:00 | 2021-09-29 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 58 | 2021-09-23 00:00:00 | 2021-09-27 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 59 | 2021-09-24 00:00:00 | 2021-09-27 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 60 | 2021-10-06 00:00:00 | 2021-10-11 00:00:00
Standard | 1 |  |  |  |  |  |  |  | Guest 61 | 2021-10-09 00:00:00 | 2021-10-13 00:00:00
Standard | 4 |  |  |  |  |  |  |  | [NAME] 62 | 2021-10-14 00:00:00 | 2021-10-20 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 63 | 2021-10-15 00:00:00 | 2021-10-24 00:00:00
Standard | 1 |  |  |  |  |  |  |  | Guest 64 | 2021-10-16 00:00:00 | 2021-10-27 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 65 | 2021-10-18 00:00:00 | 2021-10-23 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 66 | 2021-10-22 00:00:00 | 2021-10-28 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 67 | 2021-10-24 00:00:00 | 2021-10-29 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 68 | 2021-10-26 00:00:00 | 2021-10-29 00:00:00
Standard | 1 |  |  |  |  |  |  |  | Guest 69 | 2021-10-31 00:00:00 | 2021-11-05 00:00:00
Standard | 3 |  |  |  |  |  |  |  | [NAME] 70 | 2021-11-04 00:00:00 | 2021-11-09 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 71 | 2021-11-16 00:00:00 | 2021-11-22 00:00:00
Standard | 3 |  |  |  |  |  |  |  | Guest 72 | 2021-11-21 00:00:00 | 2021-11-24 00:00:00
Standard | 1 |  |  |  |  |  |  |  | Guest 73 | 2021-12-27 00:00:00 | 2022-01-02 00:00:00
Standard | 2 |  |  |  |  |  |  |  | Guest 74 | 2022-02-21 00:00:00 | 2022-02-25 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 75 | 2022-03-28 00:00:00 | 2022-04-04 00:00:00
Standard | 4 |  |  |  |  |  |  |  | Guest 76 | 2022-04-19 00:00:00 | 2022-05-02 00:00:00