Check If Time Is Between Two Times in Excel (2 Suitable Ways)

Excel is extremely powerful for various types of data handling and calculations. You can also work with time formatted data to compare between two times or the difference between two times. Besides, you can also check if an individual time is between two given times in Excel. In this article, I will show you 2 suitable ways to find if an individual time is between two given times in Excel.


Download Practice Workbook

You can download our practice workbook from here for free!


2 Ways to Check If a Time Is Between Two Times in Excel

Say, we have a dataset of 6 rows of times where one column represents the beginning times, another one represents the ending times and the other one shows some particular times. Now, we need to find if the particular time is between the respective beginning and ending times. You can follow any of the two ways described below to achieve your desired result.

Check If Time is is Between Two Times in Excel

In this article, we have used the Office 365 version of Microsoft Excel. But, no worries! You can use all of the ways described here in any version of Excel available to you. If you face any problems regarding Excel versions, please feel free to comment below.


1. Combine MEDIAN and IF Functions

The quickest way to accomplish your target in this regard is to use the MEDIAN function inside the IF function. Go through the steps below to do this.

📌 Steps:

  • First and foremost, click on the E5 cell.
  • Subsequently, insert the following formula and hit the Enter button.
=IF(D5=MEDIAN(B5:D5),"In Between",IF(D5<MEDIAN(B5:C5),"Lagging","Elapsed"))

Use the MEDIAN Function Inside the IF Function to Check If Time Is Between Two Given Times in Excel

🔎 Formula Breakdown: 

  • =IF(D5=MEDIAN(B5:D5),”In Between”

This checks if the value of the D5 cell is equal to the median of the B5:D5 cells. It would show “In Between” if the result is true.

Result: In Between

  • =IF(D5=MEDIAN(B5:D5),”In Between”,IF(D5<MEDIAN(B5:C5),”Lagging”,”Elapsed”))

If the previous test is false, then another condition will be tested if the D5 cell’s value is less than the median of the B5:D5 cells or not. If the result is true now, it would show “Lagging” and if the test is false, it would show “Elapsed”.

Result: In Between

  • As a result, the decision for the D5 cell will appear.
  • Afterward, place your cursor on the bottom right position of the cell.
  • Subsequently, a black fill handle will appear.
  • Following, drag it down to copy the same formula for all the cells below.

Drag Fill Handle Below

As a result, you will get all the decisions for all particulars time if they are in between their respective beginning and ending times in Excel. For example, the outcome should look like this.

Checked If Time Is In Between Two Given Times in Excel

Read More: Return Value If Time Is Greater Than and Less Than a Range in Excel


2. Incorporate AND, MAX, and MIN Functions Inside IF Formula

Another thing you can do to accomplish your target is to combine the AND, MAX, and MIN functions inside the IF function. Follow the steps below to do this.

📌 Steps:

  • At the very beginning, click on the E5 cell.
  • Afterward, insert the following formula.
=IF(AND(D5>=MIN(B5:C5),D5<=MAX(B5:C5)),"In Between",IF(D5<MIN(B5:C5),"Lagging","Elapsed"))

🔎 Formula Breakdown: 

  • AND(D5>=MIN(B5:C5),D5<=MAX(B5:C5))

It checks if the D5 cell’s value is greater than or equal to the minimum of the B5:C5 cells’ values and less than or equal to the maximum of B5:C5 cells’ values.

Result: TRUE

  • =IF(AND(D5>=MIN(B5:C5),D5<=MAX(B5:C5)),”In Between”

It checks if the previous check is true or false. If true, it would show the “In Between” message.

Result: In Between

  • =IF(AND(D5>=MIN(B5:C5),D5<=MAX(B5:C5)),”In Between”,IF(D5<MIN(B5:C5),”Lagging”,”Elapsed”))

It would check if the AND logic is true. If false, it would check if the D5 cell’s value is less than the minimum of B5:C5 cells. If true now, it would show “Lagging” and if false, it would show “Elapsed” now.

Result: In Between

  • Subsequently, press the Enter button.

Combine the MAX, MIN & AND Functions inside the IF Function to Check If Time Is In Between Two Times in Excel

  • As a result, you will get the decision for the D5 cell’s particular time.
  • Now, place your cursor on the bottom right position of the E5 cell.
  • Afterward, drag the black fill handle downward upon its appearance.

Drag the Fill Handle Below

Thus, you will get all the decisions for all particular times. For example, the outcome would look like this.

Checked If the Time Value Is In Between Two Times In Excel

Read More: Return Expected Value If Time Is Greater Than 1 Hour in Excel


💬 Things to Remember

  • You must format your data in the proper Time format. Otherwise, an error might happen.
  • It is better to format your time in 24-hour format. It would save you from A.M/P.M confusion and the decision-making would be easier.

Conclusion

To conclude, in this article, I have shown you 2 suitable ways to check if an individual time is between two given times in Excel. I suggest you read the full article carefully and apply the knowledge to your needs. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

And, visit ExcelDemy to learn more things about Excel! Have a nice day!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo