跳到主要內容

如何在Excel中檢查時間是否介於兩次之間？

在Excel中，如何檢查特定時間是否介於兩個給定時間之間？ 要一一檢查它們會浪費很多時間，在這裡，我將介紹一些簡單的公式來解決此工作。

使用公式檢查特定時間是否在兩個給定時間之間

箭頭藍色右氣泡 使用公式檢查特定時間是否在兩個給定時間之間

例如，我有兩列時間，分別是A列和B列中的開始時間和結束時間，現在我想檢查C列中的時間是否在A列和B列的時間範圍之間，如下圖所示：

doc檢查時間1之間的時間

以下公式可以幫助您確定特定時間是否在兩次之間，請按照以下步驟操作：

在要輸出結果的數據旁邊的空白單元格中輸入以下公式之一：

= IF（C2 = MEDIAN（A2，B2，C2），“是”，“否”）

=IF(AND(C2>=MIN(A2:B2),C2<=MAX(A2:B2)),"YES","NO")

然後將填充手柄向下拖動到要應用此公式的單元格上，並且已經確定了兩次之間的時間（如果兩次之間），則顯示“是”（如果兩次之間不是）次，將顯示“否”，請參見屏幕截圖：

doc檢查時間2之間的時間

注意： 在上述公式中， C2 是您要檢查的特定時間，並且 A2 和 B2 是包含您要檢查的兩次的單元格。

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (22)
Rated 5 out of 5 · 1 ratings
Rebecca
Rebecca
This comment was minimized by the moderator on the site
Curious how to write a formula that results a number (1) to count a person present at a specific time.
For example: If I need to know that a person was present at 2:45pm, during these 5 schedules.
IN OUT 2:45pm
11:00 5:00PM 1
12:00 4:13PM 1
9:00 5:00PM 1
7:47 2:10PM 0
3:50 6:00PM 0

Need to result a 1 - if they arrive before 2:44pm
And result a 1 - if they depart after 2:46pm

Suggestions?
Reply
0
0
Alex
Alex
This comment was minimized by the moderator on the site
Hello,

This is a great article and the OR(MOD... is a great work around for times on different dates.

How would I incorporate this to a formula with the following parameters:

If Cell A1 reads as yesterday between 22:00 and 23:59, display as same day.
If Cell A1 reads as today between 00:00 and 21:59, display as yesterday.

Any assistance here is GREATLY appreciated as I'm a lost little puppy on this one.
Reply
0
0
Rated 5 out of 5
Schonewille
Schonewille
This comment was minimized by the moderator on the site
Ik hoop dat iemand mij hier kan helpen.
Ik heb zoals de meeste mensen in deze discussie, een tijdvenster bestaande uit 2 tijden.
voor het gemak:
Cel A2 staat in 15:00
Cel B2 staat in 16:00
Cel C2 staat in 15:49

ik wil met een formule weten of Cel C2 binnen het tijdvak van A2 en B2 ligt.
als ik de formules kopieer van hierboven en deze aanpas naar mijn cellen(en taal), krijg ik een onjuiste formule melding.

Ik ben vrij handig in Excel... maar dit krijg ik verdorie niet voor elkaar.
(Ik heb een nederlandse versie van Excel)

de formule die ik nu probeerde te gebruiken is: =als(en(C2>=MIN(A2:B2),C2<=MAX(A2:B2)),"YES","NO")

wat doe ik fout? :(
Reply
Report
0
0
skyyang
skyyang     Schonewille
This comment was minimized by the moderator on the site
Hello, Bastiaan
I have tested your formula in Dutch, the formula works well, please see the below screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-check-time-1.png
Please try again, thank you!
Reply
Report
0
0
HST
HST
This comment was minimized by the moderator on the site
Hi, I am comparing hundreds of thousands of lines of data (for the first time) and needed an equation that that compares time between two of he columns. However, I cannot get away with an IF statement using A3<>F3 because the times in A3 are about 1 second off. Rather I just need to the nearest minute. Any help?
Reply
0
0
Alandrea1986
Alandrea1986
This comment was minimized by the moderator on the site
I am in need of assistance. I have been pulling my hair out over the last month trying to figure out a way to make my life simpler. I am not even sure if it can be done because I can not find anything on how to do it. My problem is this........ I recently started using a new program to collect data for incoming and outgoing employees. The data readers are sensitive and will sometimes account for an employee multiple times over the course of a couple of minutes. when I export this data into an excel file, it is an absolute nightmare trying to individually delete the extra captures. Using the remove duplicates option helps, but still leaves many duplicate times due to the Seconds in the time being different. Today for example after I removed the duplicates, I still have over 24,000 unique entries that I have to go through to get that data I need. Someone, anyone..... please Help!
Reply
Report
0
0
Maite
Maite
This comment was minimized by the moderator on the site
I am trying to calculate time per day for example I went to the computer lab and logged in for 4hours&35minutes on Monday and Tuesday I logged in on for 2 hours and 10 minutes and Wednesday 3hours and 4 minutes and Thursday for 4 hours and 14 minutes and Friday 1 hour and 15 minutes. Add those total together and get a grand total. I want to be able to keep every minute. No rounding, I know that I have to use the IF function.
Reply
0
0
Yogesh
Yogesh
This comment was minimized by the moderator on the site
Help me in this, as if there is some time ranges in a day and i have to find out time clashing is there or not. then please suggest some formula!
Reply
0
0
santoshk.rymec@gmail.com
This comment was minimized by the moderator on the site
Hello I have to find my specific time is in between two times...eg : Start time(AS2) is 23:00:00 and End time(AT2) is 8:00:00, specific time(J2) is 0:02:20, so when i use the formula =IF(AND(J2>=MIN(AS2:AT2),J2<=MAX(AS2:AT2)),"YES","NO") it should show yes as its in between tow times only but its showing no, any help in correcting the formula ?
Reply
Report
0
0
SilverFalcon
SilverFalcon    
This comment was minimized by the moderator on the site
Hi Santosh, That is similar to my use,I use something along the lines of;

=IF(AND(C2>=MIN("13:00"),C2<=MAX("13:10")),"YES","NO")
Reply
0
0
Abhishek
Abhishek     SilverFalcon
This comment was minimized by the moderator on the site
Hi Silver,


The formula doesn't work. If I have to enter "13:00" (hours) in each row, why there is formula, this is not making the job any easier.
Reply
0
0
skyyang
skyyang    
This comment was minimized by the moderator on the site
If your time over midnight, you should apply the below formula:
=OR(MOD(C1,1)>TIME(23,0,0),MOD(C1,1)<TIME(8,0,0))
Reply
Report
0
0
skyyang
skyyang     skyyang
This comment was minimized by the moderator on the site
In the above formula, C1 is the specific time you want to check for, and the TIME(23,0,0) and TIME(8,0,0) are the two times you want to check between. You should change the references to your need.
Please try it, hope it can help you!
Reply
Report
0
0
Santosh
Santosh
This comment was minimized by the moderator on the site
I have two times here 23:00:00(AS2 Start Time) and 8:00:00(AT2 End Time) and specific time is 0:02:20(J2), so when i use this formula =IF(AND(J2>=MIN(AS2:AT2),J2<=MAX(AS2:AT2)),"YES","NO"), it should come as yes as the specific time is in between our two start and end time but its showing no..any help in correcting this formula
Reply
0
0
Nikkii
Nikkii
This comment was minimized by the moderator on the site
This isn't working for me when the specific time is =NOW() to display the current time. Any suggestions?
Reply
0
0
skyyang
skyyang     Nikkii
This comment was minimized by the moderator on the site
Hello, Nikkii,

If you enter =now() function to get the current date and time, both date and time are displayed, if you need a formula to get the current time dynamically, you can apply this formula: =MOD(NOW(),1), and then format it as time format. And then, you can use the formula in this article successfully.

Please try, hope it can help you!
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations