Go Back

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.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need help creating a spreadsheet that can rank my associates in order of performance. For example, I need to assign specific categories a specific point value and then rank associates based on overall points.
Solved by Z. Q. in 13 mins
Formular for 'if cell is between 0% to 3% then assign 5 points'
Solved by E. E. in 19 mins
I need a formula to help calculate early/on time/late delivery with cushion dates
Solved by A. H. in 11 mins

Leave a Comment

avatar