Detect Duplicate Appointment Dates and Times in Excel
Question details
Check whether a requested appointment date and time is already booked and add available appointments to a summary sheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user enters an appointment date and time and needs to check availability before recording the booking.
- Observed behavior
- An existing date-and-time combination should be marked unavailable; an available appointment can be added to the summary sheet.
Create an Entry sheet with the requested date in A2 and time in B2, and a Summary sheet with Date and Time headers in A1 and B1. Enter dates and times as Excel date and time values, not text, and use consistent time precision.
Check availability with COUNTIFS and record the booking
Use a formula to flag an existing date-and-time combination, then add available appointments to the summary sheet manually.
On the Summary sheet, select the Date and Time columns, including their headers, and choose Insert > Table. On the Table Design tab, name the table Bookings.
On the Entry sheet, enter this formula in C2: =IF(OR(A2="",B2=""),"",IF(COUNTIFS(Bookings[Date],A2,Bookings[Time],B2)>0,"Unavailable","Available")).
Enter the requested date in Entry!A2 and time in Entry!B2. Check the message in C2 before accepting the appointment.
If C2 says Available, copy the date and time into a new row of the Bookings table on the Summary sheet. The formula will then mark that combination Unavailable.

Use an Excel VBA macro to check and add appointments
Use this option to check a requested slot and append it to the Summary sheet with one macro action.
Check appointment availability in WPS Spreadsheet
Use COUNTIFS in WPS Spreadsheet to identify an existing date-and-time combination, then add available appointments to your summary sheet.
- 1. Set up the summary: Open your workbook in WPS Spreadsheet. On a sheet named Summary, put Date in A1 and Time in B1, then enter existing bookings below the headers.
- 2. Enter the request: On a sheet named Entry, enter the requested date in A2 and time in B2 as spreadsheet date and time values.
- 3. Check availability: In Entry!C2, enter =IF(OR(A2="",B2=""),"",IF(COUNTIFS(Summary!$A$2:$A$1000,A2,Summary!$B$2:$B$1000,B2)>0,"Unavailable","Available")). Expand both ranges if your bookings extend beyond row 1000.
- 4. Record an available slot: If C2 says Available, copy A2 and B2 into the next empty row on Summary. The formula will then flag that exact combination as Unavailable.

Frequently Asked Questions
Why does COUNTIFS show Available for a slot that looks booked?
Check that both the requested and stored entries are real date and time values rather than text. Also check for hidden time components or differences in seconds; COUNTIFS requires exact matches.
Can a formula automatically add an available booking to another sheet?
A formula can display availability but cannot write a new booking into another cell. Copy the available appointment to the Summary sheet, or use the Excel VBA macro to check and append it.
Will the duplicate check detect overlapping appointments?
No. The formulas and macro check for the same start date and time only. Detecting overlaps requires start and end times and a different comparison.
Why does the VBA macro not run?
Open the workbook in a desktop Excel environment that supports VBA, save it as an .xlsm file, and check your macro security settings. Also confirm that the sheets are named Entry and Summary.




