Assigning Points based on Late Time in Google Sheets

It is shown in this lesson how to utilize the IF function in Excel to allocate points based on the amount of time that has passed.

assign points based on late time in google sheets1

If you intend to pursue along this guide, you may do so by downloading the template spreadsheet.

Generic formula:

=IF(C2<VALUE("0:2"),0,IF(C2<VALUE("0:35"),1,IF(C2<VALUE("0:45"),2,IF(C2<VALUE("1:00"),3,IF(C2<VALUE("3:00"),4,5)))))

assign points based on late time in google sheets1

Return value

This formula provides the number of points that have been assigned depending on the late time.

How this formula work

For example, below is a list of point regulations and time records; kindly use the formula below to find the appropriate rule or record.

=IF(C2<VALUE("0:2"),0,IF(C2<VALUE("0:35"),1,IF(C2<VALUE("0:45"),2,IF(C2<VALUE("1:00"),3,IF(C2<VALUE("3:00"),4,5)))))

A pop-up with further information will appear if you press the Enter key.

To use this formula, just drag the fill handles over the cells.

assign points based on late time in google sheets1

IF function is being used to evaluate for defined criteria and then returns the results that match those circumstances. Explain the formula in five parts, as follows:

IF (late_by_time<VALUE ("0:2"),0, It returns 0 if the late time is much less than 2 minutes.

assign points based on late time in google sheets1

IF (late_by_time <VALUE ("0:35"),1, The function returns 1 if the late time is much less than 35 minutes but higher than or equivalent to 2 minutes.

assign points based on late time in google sheets1

IF (late_by_time <VALUE ("0:45"),2, If the late time is even less than 45 minutes but higher than or equivalent to 35 mins, it returns 2.

assign points based on late time in google sheets1

IF (late_by_time <VALUE ("1:00"),3, If the late time is even less than 60 minutes but higher than or equivalent to 45 mins, it returns 3.

assign points based on late time in google sheets1

IF (late_by_time <VALUE ("3:00"),4, The result is 4 if the late time is less than 3 hours but larger than or equivalent to 60 minutes.

assign points based on late time in google sheets1

5))))) This function returns five if the late time is more than or equal to three hours.

When converting text to numbers, the value function is employed. It does this by converting the text time to a number.

Related Functions

  • Google Sheets IF function
    The Google Sheets IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Google Sheets VALUE function
    The google sheets VALUE function converts a text value that represents a number to a numeric value.The syntax of the VALUE function is as below:= VALUE  (text)…

 

Related Posts

Add Row Numbers And Skip Blanks in Google Sheets
Add Row Numbers And Skip Blanks in google sheets1

Do you ever have to input a list of numbers into a spreadsheet in Google Sheets, and some of the cells are blank? It can be difficult to keep track of what number is in which cell when you have ...

Add Months To Date In Google Sheets
Add Months To Date in google sheets1

It is important to adjust time periods when performing financial modeling. In Google Sheets, you can use the MONTH function to add a specific number of months to a date. The MONTH function takes one argument: the number of months ...

Add Leading Zeros in Google Sheets
Add Leading Zeros To Numbers in google sheets1

To pad a number with zeros, you need to add leading spaces and/or carriers. For example, if the original digit contains 2 digits, three additional characters will be added on before it. If there are 3 digits in total, allocating ...

Add Minutes to Time in Google Sheets
Add Minutes to Time in google sheets1

Adding whole hours or decimal minutes to a given time in google sheets is a fairly simple process. You can do this by using the Start_time+TIME(0,minutes,0) and Start_time+minutes/1440 functions. In this add minutes to time guide, we will show you ...

Add Hours to Time in Google Sheets
Add hours to time in google sheets1

Adding whole hours or decimal hours to a given time in Google Sheets is a fairly simple process. You can do this by using the Start_time+TIME(hours,0,0) and Start_time+hours/24 functions. In this add hours to time guide, we will show you ...

Count Cells that are Case Sensitive in Google Sheets
counting the cells that are case sensitive in google sheets1

If you are a frequent user of Google Spreadsheets, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out ...

Average Last N Values in Google Sheets
Average last 5 values in google sheets1

Average is a valuable function in google sheets that allows you to rapidly compute the average of the past N values in a column. However, you may need to insert new numbers beneath your original information from time to time, ...

Google Sheets Nexted IF Functions (Statements) Tutorial (15 IF Formulas examples)
google sheets nested if function1

This tutorial will guide you how to use nested Google Sheets IF function  (include multiple If statements in Google Sheets formula) with syntax and provide about 15 nested IF formula examples with the detailed explanation in Google Spreadsheets. Description The ...

Check If a Cell is Blank or Empty in Google Sheets
check if a range of cells is blank or empty in google sheets1

The article demonstrates how to check if a range of cells is blank or empty by ISBLANK and other functions to recognize empty cells in Google Spreadsheets and take appropriate action based on their status. There are several circumstances in ...

Calculate Interest Rate in Google Sheets
calculate interest rate1

Financial choices play a critical role in the development and execution of corporate strategies and plans. In daily life, we also face a slew of financial choices. For instance, suppose you're applying for a loan to purchase a new automobile. ...

Sidebar