In this tutorial, I will show you how to create a formula in Google Sheets to check if a specific date already exists in a list and generate the next available date if it does. We will also explore how to exclude weekends if needed.
Imagine a scenario where you need to schedule projects, book rooms, or plan events, and you want to avoid duplicate dates. With this formula, you can easily find the next available date for your bookings.
The Benefits of Generating the Next Available Date
Let me explain how this works. The formula we will create checks whether a given date already exists in a list. If it does, the formula will suggest the next available date. If the date is not already present, it will return the entered date.
This feature can be incredibly useful in various situations where booked dates are spread out. For example, let’s say you have bookings on January 1st, 3rd, 4th, 5th, and 7th, and another booking request is made for January 4th. The formula will suggest January 6th as the next available date.
By using this formula, you can avoid duplicated date entries in a date column and keep your records organized.
Next Available Date Generator Formula for Google Sheets
Here is the formula you need to check if an input date already exists and, if it does, return the next available date; otherwise, return the entered date:
=IF(ISNA(XMATCH(C2,A:A)), C2, WORKDAY.INTL(C2, 1, "0000000", A1:A))In this formula, we assume that the date you want to test is in cell C2, and the list of dates is located in column A.
Let’s break down the structure of this formula:
Anatomy of the Formula
We can divide the formula into three parts: XMATCH, WORKDAY.INTL, and the IF logical test.
Part #1: XMATCH
The XMATCH function checks whether the input date in cell C2 already exists in the list of dates in column A. If the date is found, XMATCH will return the position where the date is located; otherwise, it will return #N/A.
The XMATCH function is written as follows:
XMATCH(search_key, lookup_range, [match_mode], [search_mode])In our formula, C2 is used as the search_key, and column A:A is the lookup_range.
We’ve enclosed the XMATCH formula with ISNA to return TRUE if the search returns #N/A and FALSE if it finds a match.
Part #2: WORKDAY.INTL
The role of the WORKDAY.INTL function is to generate the next available date. It takes into consideration the specified number of workdays to add and excludes weekends and holidays.
The WORKDAY.INTL function is written as follows:
WORKDAY.INTL(start_date, num_days, [weekend], [holidays])In our formula, C2 is used as the start_date (the date in cell C2), and 1 is the num_days, which ensures the next working day is calculated.
The “0000000” in the formula specifies that no days (0) are treated as weekends. The range A1:A is used to identify holidays or non-working days, which the formula excludes when generating the next working day.
Part #3: IF
The IF function is used to determine whether the input date already exists and return the next available date accordingly.
The IF function has the following syntax:
IF(logical_expression, value_if_true, value_if_false)In our formula, the logical_expression is the output of the ISNA. If it evaluates to TRUE, the formula will return the date in C2. If it’s FALSE, the formula proceeds to the WORKDAY.INTL part (#2) to calculate the next available date.
In summary, if the input date in cell C2 is not found in the list (column A), the formula will return the same date. If the input date is already in the list, the formula calculates the next working day as the next available date.
That’s the logic behind our next available date generator in Google Sheets.
How to Generate the Next Available Date, Excluding Weekends
If you wish to exclude weekends when generating the next available date, you can modify the “0000000” part of the formula. Here’s how:
In the formula, the 7 zeroes indicate 7 workdays, where the first 0 represents Monday and the last 0 represents Sunday. To exclude weekends, replace “0000000” with “0000011.” This adjustment ensures that Saturdays and Sundays are not considered workdays when determining the next available date.
In short, a zero means that the day is a workday, and a 1 means that the day is a weekend.
Here is the adjusted formula to generate the next available date, excluding Saturday and Sunday weekends:
=IF(ISNA(XMATCH(C2, A:A)), C2, WORKDAY.INTL(C2, 1, "0000011", A1:A))
Generate the Next Available Date Based on a Condition in Google Sheets
Sometimes, you may need to generate the next available date based on a specific condition. For example, if you have hotel room numbers in column A and booked dates in column B and you want to find the next available date for room #206, you would apply a condition.
The following formula returns the next available date for room #206:
=IF(ISNA(XMATCH(D2, FILTER(B:B, A:A=D3))), D2, WORKDAY.INTL(D2, 1, "0000000", B1:B))This formula differs from the previous ones in the XMATCH part. In our earlier formulas, the lookup_range in XMATCH was simply A:A (date column). However, in this formula, it is FILTER(B:B, A:A=D3).
The FILTER function filters only the dates in B:B that match room #206 (D3) in A:A.
The syntax of the FILTER function is as follows:
FILTER(range, condition1, [condition2, ...])Next Available Date Generator Template
If you’d like to try out the next available date generator in Google Sheets using a template, click Crawlan.com to preview and copy the template for free.
The template includes three sheets:
- NAD: Next Available Date
- NAD_EW: Next Available Date (Excluding Weekends)
- C_NAD: Conditional Next Available Date (Excluding Weekends)
You can use the template in two ways:
- With an existing list of dates: Copy and paste the dates into column A in the first two sheets and column B in the third sheet.
- You only need to use one sheet.
- The locale setting of the template is “UK.” Open the copied template and click File > Settings to change the locale before using it.
- Fresh: Empty the dates in column A in the first two sheets and column B in the last sheet. Enter the first date in cell A1 or B1, depending on which sheet you use.
Input your date to test in cell C2 in the first two sheets and cell D2 in the third sheet. The criterion goes to cell D3.
To change weekends, follow the relevant portion of the tutorial above.
Now you have the tools to generate the next available date in Google Sheets. Streamline your scheduling, booking, or event planning with this powerful formula and keep your dates organized effortlessly.
