Skip to main content

How to check if time is greater than or less than a specific time in Excel?

Author: Xiaoyang Last Modified: 2020-04-22

How could you check if the time which in a list of time cells is greater than or less than a specific time in Excel? In this article, I will talk about some useful formulas to deal with this problem, please do with the following steps:

Check if time is greater than or less than a specific time with formulas

arrow blue right bubble Check if time is greater than or less than a specific time with formulas

For example, here, I will check the time based on 3:30PM, if the time is greater than 3:30PM, a text “Yes” is displayed, if less than 3:30PM, a text “No” is appeared as following screenshot shown. In Excel, the following formulas may help you, please do as this:

doc if time greater than 1

Enter this formula: =IF(A2="","",IF(A2>(--"3:30 PM"),"Yes","No")) into a blank cell where you want to locate the result, B2, for instance, and then drag the fill handle down to the cells that you want to apply this formula. Now, if the time is greater than the specific time 3:30PM, a “Yes” is displayed, otherwise, a “No” is displayed, see screenshot:

doc if time greater than 2


1. In the above formula, A2 is the cell that you want to check, and the 3:30PM is the specific time that you want to check based on, you can change them to your need;

2. Expecting the above formula, here is another formula also can help you:

=IF(A2<TIME(15,30,0),"Prior",IF(A2>=TIME(15,30,0),"After","0")), (15, 30, 0 indicate the number of hour, minute and second that you want to check based on, you can modify them to your need ), after applying this formula, if your time is greater than 15:30:00, it will displayed “After”, if the time is less than 15:30:00, the cell is displayed “Prior”. See screenshot:

doc if time greater than 3

Quickly insert random time in a range of cells:

With Kutools for Excel's Insert Random Data feature, you can quickly insert random numbers, date, times and text strings as you need.

doc insert random time

Note: More than 300 functions can make your job efficiently,you can download Kutools for Excel for free trial.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Ho to apply conditional formating
There are no comments posted here yet
Leave your comments
Posting as Guest
Rate this post:
0   Characters
Suggested Locations