WPS Spreadsheet: Create a Loan Car Booking Schedule with Conflict Alerts
Question details
The user needs a loan car booking schedule that displays vehicle availability and flags overlapping reservations when booking dates are entered or changed.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A booking manager is creating a calendar or booking table with vehicle names, start dates, and end dates, then checking new reservations against existing ones.
- Observed behavior
- The desired schedule should label a vehicle as booked or available and automatically highlight overlapping date ranges.
Create a booking table with separate columns for Car, Start Date, and End Date, and make sure all date entries are recognized as dates by WPS Spreadsheet. Reserve separate input cells for the car and date range being checked, such as H2 for the car, H3 for the start date, and H4 for the end date.
Use COUNTIFS to detect overlapping loan car bookings
Use this method when you need a clear Available or Conflict result for a selected vehicle and date range.
Two bookings overlap when they use the same car, the existing booking starts on or before the new end date, and the existing booking ends on or after the new start date. The COUNTIFS formula checks all three conditions at once.
In WPS Spreadsheet, enter Car in A1, Start Date in B1, and End Date in C1. Add existing reservations in rows 2 through 100, or extend the ranges to match your data.
Enter the vehicle to check in H2, the proposed start date in H3, and the proposed end date in H4. Format H3, H4, B2:B100, and C2:C100 as dates from the Home tab or by using Format Cells.
Select an output cell such as H5 and enter =IF(COUNTIFS($A$2:$A$100,H2,$B$2:$B$100,"<="&H4,$C$2:$C$100,">="&H3)>0,"Conflict","Available"). Press Enter to display the booking status.
Change H2:H4 to match an existing reservation and verify that H5 shows Conflict. Then enter a non-overlapping date range and confirm that it shows Available.
Highlight conflicting rows with conditional formatting
Use conditional formatting when you want every overlapping reservation in the booking table to stand out visually.
Build and Monitor Car Booking Conflicts in WPS Spreadsheet
WPS Spreadsheet can create the booking table, calculate overlap status with COUNTIFS, and apply conditional formatting so vehicle conflicts are visible as dates change. Its familiar spreadsheet tools support a practical migration from Excel booking templates.
- 1. Open WPS Spreadsheet: Launch WPS Office and choose Spreadsheet from the home screen, then open an existing booking workbook or create a blank spreadsheet.
- 2. Set up the reservation table: Add Car, Start Date, and End Date columns, enter the existing bookings, and create separate cells for the proposed car and date range.
- 3. Add the overlap check: Use the COUNTIFS formula in an output cell to return Conflict when the selected car has an overlapping reservation and Available when it does not.
- 4. Apply visual alerts: Use Home > Conditional Formatting to apply a formula-based red highlight to rows where the same car has overlapping start and end dates.
Frequently Asked Questions
How do I check whether a new car booking overlaps an existing reservation in WPS Spreadsheet?
Use =IF(COUNTIFS($A$2:$A$100,H2,$B$2:$B$100,"<="&H4,$C$2:$C$100,">="&H3)>0,"Conflict","Available"), where H2 contains the car, H3 contains the new start date, and H4 contains the new end date.
What formula highlights overlapping booking rows?
Select the booking range, open Home > Conditional Formatting, create a formula rule, and use =AND($A2<>"",COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,"<="&$C2,$C$2:$C$100,">="&$B2)>1). Apply a warning fill color.
Why does my booking formula show Conflict for the current row?
A row can match itself, so COUNTIFS may return at least one result. When checking complete booking rows, use >1 in the conditional-formatting formula, or place the proposed booking in separate input cells so it is not included in the existing reservation range.
Can I use this schedule with dates entered in different formats?
Yes, as long as WPS Spreadsheet recognizes the entries as date values. Select the date columns, choose Format Cells, select Date, and re-enter any values that remain aligned to the left or are not calculated correctly.
How can I allow same-day vehicle handoffs?
The standard formula treats an existing booking ending on the new start date, or starting on the new end date, as a conflict. To allow handoffs, change the overlap comparisons from <= and >= to < and > in both the COUNTIFS formula and the conditional-formatting rule.
