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

If Cell Contain Specific Text

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of ...

Cell Contains Number

This article will talk about how to check if a given cell contains any numbers in Microsoft Excel Spreadsheet or Google Sheets. How to quickly check if a cell contains a number? When you have a small number of cells ...

Cash Denomination Calculator

Every country has different cash denominations, so you may need to calculate the number of different denominations based on the total amount. If it is just a small amount of cash, then you can calculate the different cash denominations manually, ...

Cap percentage values between 0 and 100

This article will talk about how to cap the percentage values between 0% and 100% in Microsoft Excel Spreadsheet or Google Sheets. If you are a newbie on Excel or google Sheets, you may be able to do this by ...

Calculate Cap Percentages to Specific Value

This article will talk about how to limit the cap percentage of a given amount to a given value in Microsoft Excel Spreadsheet or Google Sheets. If you are a newbie on Excel or google Sheets, you may be able ...

Sidebar