Exceljet

Quick, clean, and to the point

Assign points based on late time

Excel formula: Assign points based on late time
Generic formula 
=
IF(time<VALUE("0:05"),0,
IF(time<VALUE("0:15"),1,
IF(time<VALUE("0:30"),2,
IF(time<VALUE("0:60"),3,
IF(time<VALUE("4:00"),4,
5)))))
Summary 

To assign penalty points based on an amount of time late, you can use a nested IF formula.In the example shown, the formula in E5 is:

=
IF(D5<VALUE("0:05"),0,
IF(D5<VALUE("0:15"),1,
IF(D5<VALUE("0:30"),2,
IF(D5<VALUE("0:60"),3,
IF(D5<VALUE("4:00"),4,
5)))))

Note: line breaks added for readability.

Explanation 

This formula is a classic example of a nested IF formula that tests threshold values in ascending order. To match the schedule shown in G5:G11, the formula first checks the late by time in D5 to see if it's less than 5 minutes. If so, zero points are assigned:

IF(D5<VALUE("0:05"),0,

If the result of the logical test above is FALSE, the formula checks to see if D5 is less than the next threshold, which is 15 minutes:

IF(D5<VALUE("0:15"),1,

The same pattern repeats at each threshold. Because the tests are run in order, from smallest to largest, there is no need for more complicated bracketing.

The VALUE function is used to make Excel treat time value at each threshold as a number instead of next.

Note: you can also use VLOOKUP to replace nested IFs if you like.

Author 
Dave Bruns
Related formulas 
Excel formula: Nested IF function example
Nested IF function example
This article describes the Excel nested IF construction. Usually, nested IFs are used when you need to test more than one condition and return different results depending on those tests. Testing more than one condition If you need to test for more...
Excel formula: Invoice status with nested if
Invoice status with nested if
Note: the "current date" (i.e. today) for this example is May 31, 2016 With nested IF statements, the flow is from outer IF statements to inner IF statements, and the challenge is always to construct the flow so that the formula returns a logically...
Related functions 
Excel IF function
Excel IF Function

The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...

Excel VALUE function
Excel VALUE Function

The Excel VALUE function converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value. Normally, the VALUE function is not needed in Excel, because Excel automatically converts text to numeric...

Related videos 
How to create a formula with nested IFs
In this video, I'll show you how to create a formula that uses multiple, nested IF statements. This is a common technique to handle multiple conditions in a single formula.
How to make a nested IF formula easier to read
By their nature, nested IF formulas are hard to read. In this video, we show you how to easily make a nested IF formula more readable, by adding line breaks.
See also 
19 tips for nested IF formulas

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.