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.

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)))))

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.

Related formulas

Excel formula: Nested IF function example

Nested IF function example

The goal is to assign a grade to each score in column C according to the rules in the table in the range F4:G9. One way to do this in Excel is to use a series of nested IF functions. Generally, nested IFs formulas are used to test more than one condition and return a different result for each...
Excel formula: Invoice age and status

Invoice age and status

The goal is to calculate the correct invoice status ("OK", "Paid", or "Overdue") using the following rules: If there is an "x" in the "Paid" column, return "Paid". If there is not an "x" in the "Paid" column, and if the age is less than 31 days, return "OK" If there is not an "x" in the "Paid"...

Related functions

Excel IF function

IF Function

The Excel IF function performs a logical test and returns one result when the logical test returns TRUE and another when the logical test returns FALSE. 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

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 values.

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. Let's take a look. This worksheet shows a class of students with five test scores in columns D through H, and an average in column I. In column J...

How to make a nested IF formula easier to read

In this video we're going to look at how to make a nested IF formula more readable by adding line breaks. Here I have a worksheet that calculates sales commissions based on the commission structure shown in the table. For example, we can see that King sold $124,500 and gets a commission of 5%,...
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions

Videos

Articles