엑셀 회의실 예약 시간표 만들기: 확정·취소 기록으로 빈 시간과 겹침 보기

To create a schedule for office meeting rooms, start by maintaining a reservation ledger. Create an hourly timetable, displaying the reservation status every 30 minutes. This enables you to view both empty slots and overlapping reservations in the same room. Using AI, a sample of five virtual reservations was generated, with calculations and time intervals verified in Python.

The actual Excel application or reservation service was not used for these examples. "Empty" represents an absence of entered confirmed reservations, and does not reflect actual availability.

1. Record daily reservations in a sheet named "Reservation" and "Status", with I1 containing the target date and I2 set to 2026-11-12. List one reservation per row, and use KST (UTC+09:00) time for the example, focusing on Meeting Room A and B. Record one reservation per row, and avoid copying the block. Input cell C and D as Excel time values, formatted as hh:mm. The format of rows 2~101 in sheet "Reservation" is B2:B101, C2:C101, D2:D101, and E2:E101.

2. Count confirmed reservations by room. In the "Status" sheet, set B1 to "Meeting Room A" and C1 to "Meeting Room B". Input the times below, directly inputting 09:00, 09:30, 10:00, 10:30, 11:00, and 11:30 in cells A1 to F1. From cell B2, input the following formula: =COUNTIFS(Reservation!$B$2:$B$101,B$1,Reservation!$C$2:$C$101,$A2,Reservation!$D$2:$D$101,$A2,Reservation!$E$2:$E$101,"Confirmed"), extending to row 7.

The condition checks for the same room, start time ≤ the cell time, end time = the cell time, and status = "Confirmed". The range is set to 2~101 rows. The end time is not included, so two reservations starting at the same time are not counted as overlaps.

3. Display the reservation schedule clearly. In cell E1, enter "Time", F1 "Meeting Room A", and G1 "Meeting Room B". From E2 to E7, enter the formula E7 to E2, and set the format to hh:mm. From F2 to G2, extend the formula to 7 rows. B:C displays the count, while E:G shows the schedule. The interpretation is as follows: 09:00~09:30, Meeting Room A: one confirmed reservation, Meeting Room B: empty (0).

09:30~10:00, Meeting Room A: one confirmed reservation, Meeting Room B: one confirmed reservation. 10:00~10:30, Meeting Room A: one confirmed reservation, Meeting Room B: one confirmed reservation. 10:30~11:00, Meeting Room A: two overlaps, Meeting Room B: empty (0). And so on.

4. Update the ledger for canceled or added reservations. To cancel R03, change the reservation status in row 4 of "Reservation" from "Confirmed" to "Canceled". The count in row 10 will change from 1 to 0 in Meeting Room A, and 0 in Meeting Room B. Append a new row for the cancellation, keeping the existing confirmed reservation rows intact.

Continue by entering R06 / Meeting Room B / 11:00 / 12:00 / Confirmed in cells A~E of row 7. The existing range (2~101) automatically adjusts, and the formula in E:G recalcultes without modification. Always ensure proper input formatting, and avoid adding unnecessary spaces or special characters to room names.

5. To reuse the template for a new day, copy the empty layout, change the date in I2, and input the new reservations. Maintain the same format, and ensure the date changes only. This allows you to quickly identify any missing data entry. For entries beyond row 102, expand the COUNTIFS ranges in B, C, D, and E to match the new row. Adjust the time ranges and calculation formulas accordingly if the operating hours or intervals change.