ElyxAI

Vehicle availability in Excel: status and the next date

Fleet ManagerVehicle Availability

A fleet availability board is one row per vehicle. You type an identifier, a status, the driver if someone has it, and the next date you are watching. The status is a word from a list: available, out, or workshop. The due flag compares the next date with today. COUNTIFS counts the rows whose status is available. This board is not the service history. A history is one line per oil change, inspection, or repair, and it belongs in a log where a vehicle can have many lines. If you put that history on this board, one vehicle becomes many rows and you can no longer answer "is this vehicle free today" by filtering a single status. Keep the next date here. Keep the past services in the log. The sheet does not know a legal interval. You type the next date, whether that date came from a document, a workshop booking, or a reminder you chose. IF flags the row when that date is on or before today, or when it falls inside a window you typed in a cell, such as 14 days. Change the window in that cell. Do not hide 14 inside the formula. Before you assign a vehicle, read the status and the due flag on that row. Available with a due flag still means you have a date problem, not that the vehicle is clear to send. Out means someone already has it. Workshop means it is not yours to assign. The words only work if everyone uses the same three. Check three vehicles you can see in the yard or on the key board. The status on the sheet must match where the vehicle actually is. The next date must match the date on the document or the booking, not a date you remember. Elyx can write the due flag and the count. You still type the status and the date. The trial lasts 7 days, with no card.

The Problem

The board fails when status is a sentence. "Back Thursday if the part arrives" cannot be counted. Put the sentence in a note. Put available, out, or workshop in the status. The count reads the status. It also fails when each morning starts a new row for the same vehicle. You then have five statuses for one van and the latest one is whichever row you happen to see. One vehicle, one row. You edit the status in place. The note can say what changed. A third failure is calculating the next service date from a mileage formula you do not maintain. If the odometer on the sheet is old, the predicted date is fiction. This board uses the date you typed. If you also keep mileage, it is a number you update. It does not, by itself, invent the next booking.

Benefits

One row per vehicle, edited in place when the status changes.

Status is one of three words, so COUNTIFS can count what is free.

The due flag compares a date you typed with today.

The warning window is a cell, not a number buried in the formula.

Past services stay in a log so the board does not grow a row per job.

Step-by-Step Tutorial

1

Lay out one row per vehicle

Name the sheet Board. Headers in row 1: Vehicle, Registration, Status, Driver, NextDate, Due, Odometer, Note. Vehicle is the name your dispatchers already use. Registration is the plate or fleet number, unique. Status, Driver, NextDate, and Note are entries. Due is a formula. Odometer is optional and is only the last number you typed. One vehicle occupies one row for as long as you operate it. Add a row when a vehicle joins the fleet. Do not add a row because a new day started.

Sort by Vehicle and scan for duplicate registrations before you rely on the count. Two rows for one van will be counted twice.

2

Restrict the status words

On a sheet named Lists, type available, out, and workshop in three cells. Apply data validation to the Status column so only those words can be entered. Available means you can assign it. Out means it is already on a job. Workshop means it is not available, whatever the reason: service, inspection, or a repair. If you need more detail, use Note. Do not add a fourth status unless you also change the COUNTIFS that defines 'free'. Agree the list before the first week of use.

Validation stops new typos. It does not fix a status that was pasted before the validation existed. Clean those cells once.

3

Type the next date you actually have

NextDate is a real date: the document expiry, the booked service, or the date you decided to review the vehicle. Leave it blank if you do not have a date. Do not type a guess to make the column look full. Format the column as a date. A date entered as text, for example a month name without a year the sheet can parse, will not compare with TODAY. If you are unsure, retype the date in the cell and check that it is right-aligned as a number of days, not left-aligned as text.

Keep one next date, the soonest one you care about. A second date, such as insurance versus service, needs a second column, not two dates in one cell.

4

Flag a date that is due

Put the warning window in a cell named Window, for example 14, meaning you want to see dates that fall within 14 days as well as dates already past. With NextDate in E2, the flag is =IF(E2="","",IF(E2<=TODAY()+Window,"due","ok")). A blank date stays blank, so a vehicle with no date is not marked ok. A date on or before today plus the window is due. Any later date is ok. Change Window when you want a longer look-ahead. The formula on every row reads that one cell.

=IF(E2="","",IF(E2<=TODAY()+Window,"due","ok"))

Name the cell Window with Formulas, Define Name, so the formula stays readable. If you move the cell, update the name.

5

Count vehicles you can assign

Free, for this board, means status available. =COUNTIFS(C:C,"available") is that count. It does not look at the due flag. A second count, vehicles that are available and already due, is =COUNTIFS(C:C,"available",F:F,"due"). Read both. The first is the pool. The second is the pool you should look at before you hand out keys, because a date you typed is already inside the window. Workshop rows are not in either count. Out rows are not in either count.

=COUNTIFS(C:C,"available",F:F,"due")

Put the two counts at the top of the sheet and label them in the next cell, in words, so a substitute dispatcher does not have to read the formula.

6

Show the days until the date

A day count helps the sort. =IF(E2="","",E2-TODAY()) returns a positive number of days until the date, zero if the date is today, and a negative number if the date has passed. Format it as a number. Sort this column from smallest to largest when you are working through due vehicles. Do not replace the due flag with this number. The flag is the word the COUNTIFS uses. The day count is for sorting and for reading.

=IF(E2="","",E2-TODAY())

INT is unnecessary if both values are dates without times. If NextDate was entered with a time, wrap the subtraction in INT.

7

Update status when the vehicle moves

When a driver takes the vehicle, change Status from available to out and type the driver. When it returns, set Status back to available and clear or replace the driver. When it goes to the workshop, set Status to workshop and type the return booking into NextDate if you have one. Do not leave Status as available because you expect the vehicle back this afternoon. The board describes where it is now. The note can say when you expect it. Expectation is not a status.

If two people edit the file, agree that the last person to see the vehicle updates the row before they leave the yard.

8

Check three vehicles before you publish the count

Pick three rows. For each one, look at the vehicle or the key board. Status must match. If NextDate is filled, the date must match the document or the booking in front of you. The due flag must be due when that date is inside the window, and blank when NextDate is empty. If one row fails, do not send the available count. Fix the cell. The count is the count of the words on this sheet. It is not a statement about whether the fleet is large enough.

A service log can hold the old jobs. When a service is done, type the new next date on this board. Do not paste the log's rows onto the board.

Template Features

One current status

Status is available, out, or workshop. COUNTIFS counts available. Note holds the sentence that does not fit the word.

Due flag from a date you typed

The flag is due when NextDate is on or before today plus the window. A blank date does not become ok.

=IF(E2="","",IF(E2<=TODAY()+Window,"due","ok"))

Window in one cell

Change the named cell Window to look further ahead. Every row reads it. You do not edit the formula.

Available and due

A second COUNTIFS counts vehicles that are both available and due, so the pool and the date problem stay separate.

=COUNTIFS(C:C,"available",F:F,"due")

Days until the date

NextDate minus today is the sort key. Negative means the date has passed.

=IF(E2="","",E2-TODAY())

Concrete Examples

A vehicle that is free but due

You have a van in the yard. The service date you typed is tomorrow. The window is 14 days. You want the board to show both facts.

Status: available. NextDate: tomorrow. Window: 14.

Result: Due is due, because tomorrow is within 14 days. The available count includes the row. The available-and-due count includes it too. You can still assign it, and you can see the date before you do.

A vehicle with no date yet

A new vehicle has no document date entered. You do not want it marked ok.

Status: available. NextDate: empty. Window: 14.

Result: Due is blank. Days until the date is blank. The available count includes the row. The available-and-due count does not, because the flag is not the word due. Fill the date when you have it.

A vehicle that left this morning

A driver took the keys. The row still says available because nobody edited it.

You set Status to out and type the driver's name. NextDate stays as it was.

Result: The available count drops by one. The due flag does not change, because it reads the date, not the status. The row remains one row. You did not add a new line for the trip.

Pro Tips

Registration is the key

Dispatchers may shorten a vehicle's name. The registration or fleet number should be unique and stable. If you look a vehicle up, look it up on that column.

Odometer is an entry

If you keep mileage, type the latest reading when you have it. Do not let a formula add an assumed daily distance. A made-up odometer will be used as if it were read.

Insurance and service are two dates if you track both

Add a second date column and a second flag if you truly watch two dates. Do not pack both into NextDate. The flag can only compare one date.

Archive a sold vehicle by status, or by moving the row

If a vehicle leaves the fleet, cut the row to a sheet named Left, or add a status you exclude from every count. Leaving it as available keeps it in the pool.

Formulas Used

Don’t want to do it by hand? Ask Elyx

Example request

“Build this in my workbook: Fleet vehicle availability, with the formulas, totals and formatting”

Elyx does it directly in your Excel file.

7 days free · No credit card required

Related guides

Frequently Asked Questions