Get instant live expert help with Excel or Google Sheets “My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

#### Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

# Assign points based on late time

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.

### Did this post not answer your question? Get a solution from connecting with the expert. 