There are times when we need to assign penalty points on late time. To do this, we can utilize a nested IF formula. This post provides a clear guide on how to use this function to **assign points on late time** in excel.

*Figure 1: How to assign points on late time*

**General syntax of the 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(“6:00”),4,5)))))**

Where;

**Time-** is the “late by” time

**How the formula works**

This formula tests the threshold values in an ascending order. The formula first checks the “late by” column, which is column D2:D10 in the above example, before returning an answer for the late points.

In our case above, the formula for cell E2 is

** =IF(D2<VALUE(“0:05”),0,**

- If the condition is satisfied, that is if it is less than 0:05, then it will assign it zero points.
- In the event that the logical test turns out to be FALSE, the formula checks D2 is less than the next threshold, which is 0:15, then it will assign it 1 points.
- This pattern keeps repeating itself for each threshold.
- We use the VALUE function in the formula to enable it treat the threshold as a number and not as text.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment