Function Problems

Detect Duplicate Appointment Dates and Times in Excel

Anum MushtaqAnum Mushtaq Nov 18, 2026 868 views

Question details

Check whether a requested appointment date and time is already booked and add available appointments to a summary sheet.

Detect Duplicate Appointment Dates and Times in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Create a bookings table

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.

2
Enter the availability formula

On the Entry sheet, enter this formula in C2: =IF(OR(A2="",B2=""),"",IF(COUNTIFS(Bookings[Date],A2,Bookings[Time],B2)>0,"Unavailable","Available")).

3
Check the requested slot

Enter the requested date in Entry!A2 and time in Entry!B2. Check the message in C2 before accepting the appointment.

4
Add an available 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.

Check availability with COUNTIFS and record the booking
Formula scope: COUNTIFS checks for an exact match on both values. It does not detect overlapping appointment durations, and the formula does not write bookings to the summary sheet automatically.
WPS Spreadsheet

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. 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. 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. 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. 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.
Check a requested appointment against existing date-and-time pairsOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interface to maintain a booking summary
microsoft office alternative - wps office

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.