How to Determine in Excel whether a Date Is Between Two Given Dates (Easy Formula):

Excel data analysis frequently requires dealing with dates.

Many Excel users frequently need to determine whether a date falls between two specified dates when working with dates.

Determining whether a report submission date was within the specified dates or not is a straightforward use of this. Using this information, you may then emphasize the reports that were sent in after the deadline.

I’ll demonstrate how to determine whether a date falls between two supplied dates in this lesson.

This instruction explains:

MS Excel VBA

1. Applying the Nested IF Formula 2. Using the formula IF + AND 3. Verify if the date falls on a weekend. 4. Problems in Determining whether a Date Is Between Two Dates         4.1 Dates Must Be Formatted Correctly             4.2 Dates may have a concealed time component.

Applying the Nested IF Formula

Using a straightforward if the formula is one of the simplest techniques to determine whether a date falls between two specified dates.

We would also need to use two formulas since we need to check for two circumstances.

The nested IF construct is what you use when you utilize one IF formula within another IF formula.

I’ve included a data set below with the start and end dates of the project listed in columns A and B, respectively. The project submission date is listed in column C after that.

Right now, I want to make sure that the project submission date fell inside the project’s start and finish dates.

The stacked IF expression shown below makes doing this simple:

=IF(C2>=A2,IF(C2<=B2,"In Range","Out of Range"),"Out of Range")

The formula, as mentioned earlier, would return “In Range” if the date falls within the range of the two specified dates and “Out of Range” if the date falls either ahead of the project start date or behind the project end date.

Let me now briefly explain how this formula functions.

I initially used the and if formula to determine if the date is after the project start date.

I need to describe what the formula should do if this condition is proper based on these criteria and what it should do if this condition is false.

However, because there are two requirements to be verified, after verifying the first condition, I utilize the second IF function to verify the second condition (which is whether the date is before the project end date or not).

Given that the IF function requires three arguments—the condition, the value when the situation is True, and the value when the state is False—I specify “In Range” as the second argument for the second IF function because it meets both conditions, and “Out of Range” as the third argument because it meets the first if state but fails the second.

MS Excel VBA

The second parameter for the first IF function is then set to “Out of Range” as the last step (which means that the condition in the first IF function failed, and hence it did not go to the second if function and instead returned the second argument of the first IF function).

Since I only needed to verify two criteria, I utilized two if functions,
with the second function nesting within the first. 
You can further layer these functions if you need to check more than two requirements 
(although it tends to get a bit complicated after a few)

Also read: MS Excel VBA Tutorial

Using the formula IF + AND

While many Excel users are quite at ease using the IF formula, I prefer to use the combination of the IF and AND formulas when there are multiple conditions to check.

You may define the outcome you should obtain if all the circumstances are true, the outcome you should get if any of the conditions are false, and you can check for several conditions within the AND formula.

Take the same set of data once more, this time with the project start and finish dates in columns A and B and the project submission date in column C.

The formula I may use to determine if the submission date falls inside the project start and finish dates are shown below:

MS Excel VBA

=IF(AND(C2>=A2,C2<=B2),"In Range","Out of Range")

The aforementioned formula checks for both circumstances and returns “In Range” if the submission date falls between the start and end dates, and “Out of Range” if the submission date falls either before the project began or after the project ended.

Although I didn’t need to utilize two IF functions, one IF function was still used in the calculation above.

I used the AND function to check for both criteria as I needed to do so.

If either or both of the circumstances are false, the AND function will return FALSE. If both or either of the requirements is true, it will return TRUE.

And I used an IF function that would give me “In Range” if the result was TRUE and “Out of Range” if the result was FALSE since I required a more detailed output than a basic TRUE or FALSE.

MS Excel VBA

Verify if the date falls on a weekend:

MS Excel VBA Tutorial

I would define the weekend days for the sake of this lesson as Saturday and Sunday.

The equation that will help you with this is given below:

MS Excel VBA

=WEEKDAY(A2,2)>5

It will give you a TRUE if the date falls on a Saturday or Sunday; otherwise, it will give you a FALSE.

The WEEKDAY algorithm mentioned above determines the weekday number for a given day by examining the date’s serial number. Thus, if it were a Monday, it would return 1, a Tuesday, 2, and so on.

The result would be TRUE if the date is on a weekend and FALSE if it is on a weekday because the condition I’m checking is whether the weekday result is greater than 5.

Use the following if formula to return “Weekday” if the date falls on a weekday and “Weekend” if it falls on a weekend if you want the result to have more meaning.

MS Excel VBA  MS Excel VBA Tutorial

=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")

Problems in Determining whether a Date Is Between Two Dates:

I’ve already demonstrated a few situations where you can determine whether a date falls between two provided dates.

The fundamental idea behind each formula is to compare the value of the provided date to that of the start and end dates. If the supplied date’s value falls inside the range of the start and finish dates, it falls within that range; otherwise, it does not.

However, occasionally you could have some unexpected outcomes.

You should be aware of the following typical mistakes when comparing dates in Excel by reading this section:

Dates Must Be Formatted Correctly:

In Excel’s back end, dates and time data are kept as numbers. In essence, you are comparing two numbers when you compare two dates.

You could believe that a date, such as January 1, 2023, doesn’t appear like a number, but keep in mind that dates are styled differently in Excel cells even if they are still numbers in the background.

For instance, a cell may display January 1st, 2023, while its value in the background would be 44927. (which indicates the total number of days that have elapsed after 01 Jan 1900).

Now, everything should be OK if your dates are formatted correctly.

However, it’s also possible that your date is formatted in a way that Excel does not recognize as a date, in which case it will be treated as a text string rather than a number in the back end.

MS Excel VBA Tutorial

MS Excel VBA

In Excel, for instance, January 1, 2023, is not an acceptable date format.

Therefore, using this to compare it with other dates might produce inaccurate results if you put it in an excel cell and assume it to be a text string.

In conclusion, ensure that the dates in Excel are formatted correctly before comparing them.

Dates may have a concealed time component:

Time values are likewise kept in Excel’s database as numbers, just like dates are.

A whole number represents a whole day, whereas the decimal element represents a percentage of that day or its temporal value.

For instance, if a cell in Excel had 01-01-2023 18:00:00 in it, the backend would have 44927.75, where 44927 stands for 01-01-2023 and 0.75 for 18:00:00.

Here is the issue that might arise when determining if a date falls between two specified dates.

Even when it’s obvious that the date falls within the two specified dates, Excel can return a different answer.

This is possible because the time component is often suppressed when working with dates and times, leaving you merely with the date and omitting the time value.

MS Excel VBA Tutorial MS Excel VBA

In the straightforward example below, cells A1 and B1 contain the same date, but when I compare them, it indicates that they are not the same.

While it is obvious that both dates are precisely the same, you cannot see that field A1 has a time value that is concealed from view. However, Excel views these two cells as distinct when comparing them (which it rightly should).

Although this is a rare occurrence, it can occasionally baffle even the most experienced Excel users.

In this lesson, I’ve shown you a few straightforward formulae you may use to determine if a date falls inside a range of dates.

I’ve also covered a few hazards to watch out for when comparing dates in Excel.