Matching dates in excel

Check IF a Date is Between Two Given Dates in Excel (Easy Formula)

Data analysis in Excel often involves working with dates.

A common thing many Excel users need to check when working with dates is whether a date is between two given dates.

A simple use case of this could be when you need to check whether the date of submission of a report was within the given dates or not. Based on this, you can highlight what reports were submitted after the deadline.

In this tutorial, I will show you how to check if the date is between two given dates or not.

This Tutorial Covers:

Using Nested IF Formula

One of the easiest ways to check whether a date is in between two given dates is by using a simple if formula.

And since we need to check for two conditions, we would need to use two if formulas.

And when you use an IF formula within another IF formula, that is called the nested IF construct.

Below I have a data set where I have the project start date and project end date in column A and column B respectively. And then I have the project submission date in column C.

Now I want to check whether the project submission date was between the project start and project end date or not.

This can easily be done using the below nested IF formula:

The above formula would return ‘In Range’ if the date lies in between the two given dates, and it would return ‘Out of Range’ in case the date is either before the project started or after the project end date.

Now let me quickly explain how this formula works.

I first used and if formula to check whether the date is after the project start date or not.

Based on these criteria, I need to specify what should the formula do in case this condition is true, and what should the formula do in case the condition is not true.

But since I have two conditions to check, immediately after I checked the first condition, I use the second IF function to check for the other condition (which is whether the date is before the project end date or not).

Since the IF function takes 3 arguments (the condition, value when the condition is True, and value in the condition is False), for the second IF function, I specify ‘In Range’ as the second argument, as it has satisfied both the conditions, and I specify out of range as the third argument, because it satisfies the first if condition, but it fails the second if condition.

And then finally, I specify ‘Out of Range’ as the second argument for the first IF function (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 had only two conditions to check, I have used two if functions where the second function is nested within the first one. In case you have more than two conditions to check you can further nest these if functions (although it tends to get a bit complicated after a few)

Using IF + AND Formula

While many Excel users are quite comfortable using the IF formula, when you have multiple conditions to check, I prefer using the combination of IF and AND formula instead.

Within the AND formula, you can check for multiple conditions, and can specify what result you should get in case all the conditions are true, and the result you should get in case any of these conditions are FALSE.

Let’s again take the same data set where I have the project started and project end date in column A and column B, and I have the project submission date in column C.

Below is the formula I can use to check whether the submission date lies between the project start date and project end date or not:

The above formula checks for both the conditions and it would return ‘In Range’ in case the submission date is in between the start and the end date, and it would return ‘Out of Range’ in case the submission date is before the project started or after the project end date.

Note that I have still used an IF function in the above formula, however, I didn’t have the need to use two if functions.

Since I had to check for both the conditions, I did that using the AND function instead.

The AND function would return TRUE if both the conditions are true, and it would return FALSE if any or both the conditions are false.

And since I needed a more descriptive output instead of a simple TRUE or FALSE, I have used an IF function where it would give me ‘In Range’ in case the result is TRUE and ‘Out of Range’ in case the result is FALSE.

Check If the Date Occurs on Weekend

A common use case when working with dates in project management is to identify whether a date occurs on a weekend or not.

If you’re checking this manually, you would check whether a date is a Saturday or Sunday.

But when working with dates in Excel, you can use the WEEKDAY function that can easily do this for you.

Below I have a data set where I have some dates in column A, and I want to check whether these dates occur on a weekend or not.

For the purpose of this tutorial, I would consider Saturday and Sunday as the weekend days.

Below is the formula that will do this for you:

If the date occurs on a Saturday or Sunday, it will give you a TRUE, else it will give a FALSE.

The above WEEKDAY formula checks the serial number of the date, and returns a number that corresponds to the weekday number for that date. So if it’s a Monday, it would return 1, if it’s a Tuesday it would return 2, and so on.

Since the condition that I’m checking is whether the weekday result is more than 5, it would return TRUE if the date is on a weekend, and it would return FALSE if it’s on a weekday.

If you want the result to be more meaningful, you can use the below if formula, which will return ‘Weekday’ in case the date occurs on a weekday and ‘Weekend’ in case the date occurs on a weekend.

Issues When Checking Whether a Date is in Between two dates

So far, I’ve shown you a couple of scenarios where you can check whether a date lies between two given dates.

In all the formulas, the underlying principle is to compare the value of the given date with the start and the end date. If the value of the given date, is in between the start and the end date, then it occurs in between these two dates, else it does not.

However, in some cases, you may see some unexpected results.

In this section, I cover some common pitfalls that you need to be aware of when comparing dates in Excel:

Dates Need to Be in the Right Format

Dates and time values are stored as numbers in the back-end in Excel. So when you compare two dates, you’re essentially comparing two numbers.

You may think that the date looks nothing like a number (for example 01 January 2023), but remember that dates are formatted to show up differently in the cell in Excel, while in the back-end these are still numbers.

For example, a cell may show 01 January 2023, but in the backend, the value of this cell would be 44927 (which indicates the total number of days that have elapsed after 01 Jan 1900).

Now, if your dates are in the right format, all is well.

But there is also a possibility that your date is in a format that Excel does not recognize as a date, which means that instead of a number in the back end, it ends up being considered a text string.

For example, Jan 01, 2023, is not a valid date format in Excel.

So if you have this in a cell in excel, it would be considered a text string, and using this to compare it with other dates can give you incorrect results.

Bottom line – When comparing dates in Excel, make sure that the dates are in the right format

Dates May have a Time Part that’s Hidden

Just like dates, time values are also stored as numbers in the backend in Excel.

While a whole number would indicate a full day, the decimal part would indicate the portion of the day or the time value for that day.

For example, if you have 01-01-2023 18:00:00 in a cell in Excel, in the backend it would be 44927.75, where 44927 means 01-01-2023 and 0.75 means 18:00:00

Now, here’s the problem that you can encounter when checking whether a date lies in between two given dates.

You may clearly see that the date is in between the two given dates, but Excel may give you a different result.

This can happen because most of the time when people work with dates and times, the time portion is hidden so that you only see the date but you do not see the time value.

Below I have a simple example where I have the same date in cells A1 and B1, but when I compare these two cells, it tells me these are not the same.

While you can clearly see that these dates are exactly the same, what you do not see is that there is a time value in cell A1 that is hidden so you do not see it. But when excel compares these two cells, it considers these as different (which it rightly should).

While this is not such a common scenario, if this happens, it can sometimes stump even advanced Excel users.

In this tutorial, I showed you a couple of simple formulas that you can use to check whether a date is between two given dates or not.

I’ve also mentioned some of the pitfalls you should be aware of when comparing dates and Excel.

I hope you found this Excel tutorial useful.

Other Excel Tutorials you may also like:

Источник

Compare Dates in Excel

Excel Compare Dates (Table of Contents)

Introduction to Compare Dates in Excel

Working as a Data Analyst who always have to deal with a huge amount of data, you might have faced situations where you need to compare two dates. Either you are interested in checking whether the dates are matching with each other in two different columns or not, or you maybe wanted to check if one of the date values is lesser than or greater than the other. Sometimes, you may have different testing criteria for two date columns based on the requirement of analysis or need of the hour, we must say. Well, comparing two dates in Excel is a simple task and may not always require a lot of effort (unless and otherwise, the criteria are different as well as difficult than usual.

Excel functions, formula, charts, formatting creating excel dashboard & others

What is Compare Dates in Excel?

When you need to compare two date values present in two different columns for equality, or you need to check if dates from one column are greater or lesser than the other, such scenarios can be considered as the ones which you can call the date comparison scenarios. It also varies from situation to situation (for which we use the conditional IF statement). Let’s see some of the examples where we make a comparison between dates and get useful insights for the better realization of the topic.

Examples of Compare Dates in Excel

Lets us discuss Compare Dates in Excel with Some Examples.

Example #1 – Comparing if Two Date Values are Matching or Not

Suppose we have data as shown below, which consists of two columns that have different date values. We need to check whether the two date values match each other or are not matching with each other. Follow the steps below to check whether each date value from two columns is matching with each other or not.

Step 1: Initiate a formula under cell C2 by typing equals to sign as shown below:

Step 2: Since we wanted to check whether date values between cell A2 and B2 are matching or not, we can do it by using the simplest of all comparison operators named equals to. In use “A2 = B2” in cell C2 after the initial equals operator.

The formula in cell C2 checks if the values in cell A2 and B2 are matching with each other or not. If the values are matching, it will return Boolean output as TRUE. If values are not matching, it will return a Boolean output as FALSE.

Step 3: The formula is complete. press Enter key and drag the formula across the cells to see the output of the formula. You can drag the formula using Ctrl + D shortcut. However, for that, you need to select all the cells in column C across C2 to C11.

You can see for those cells where date values are matching with each other, the system generates a Boolean output as “TRUE”, and for the cells where date values are not matching, it generates the output as “FALSE”.

If you would have noticed, cell C gives the output as TRUE. Even if the values from cell A2 and B2 doesn’t seem to be matching to naked eyes. Actually, both of the values are the same. This is just a difference in format. The date values are being stored in a number of days from seed date 01-01-1900 under Excel. Thus, 43943 is nothing but a difference in the number of days for date 22-04-2020 from the seed date. That’s the reason they are matching with each other.

Example #2 – Comparing if Dates are Greater than or Less than the Other Dates

Now, we will move a step further and check whether date values from one column are greater than or less than the date values from another column. This can be done with the help of Greater Than (“>”) and Less Than (“ B2”. Well, as the formula itself speaks, this will check if the date value under cell A2 is greater than the date value under cell B2 or not. If it is, the system will generate a Boolean output as TRUE. If it is not, then the system will generate a Boolean output as FALSE.

Drag the formula across rows to get the output for all cells in column C.

Similarly, you can check whether date values from column A are lesser than those of column B. All you need to do is replace the Greater Than (“>”) operator with Lesser Than (“ Popular Course in this category

Источник