In Google Sheets, I want to be able to calculate a dollar amount based on the time of day entered.

I'd like to enter the time in one cell and have the formula in another cell calculate the cost.

These are the time ranges:

  • 2:36-3:05 $3
  • 3:06-3:35 $6
  • 3:36-4:05 $9
  • 4:06-4:35 $12
  • 4:36-5:05 $15
  • 5:06-5:30 $18

Below is an example showing the result I want:

A    B                   
1 Time Result
2 3:30 $ 6.00
3 4:00 $ 9.00
4 2:37 $ 3.00

How can I perform this calculation?

  • 1
    Welcome to Web Applications! We'd love to help you but you ned to add more info. It seems like you may be looking for a simple IF function (link). Checkout that link and see if it helps. If you need more help Edit the question to include sample data and expected results. Also include the formula you are struggling with and let us know what did/didn't work and any errors you encountered. If you want to get the most out of this site, take the quick site Tour and review How to Ask.
    – Blindspots
    Sep 27 at 18:11

1 Answer 1



=VLOOKUP(A2, $D$2:$E$7, 2)    // Basic

=ARRAYFORMULA(VLOOKUP(A2:A4, D2:E7, 2))    // Array

=IFNA(ARRAYFORMULA(VLOOKUP(A2:A, D2:E7, 2)))    // Error Handling


This is an excellent candidate for the VLOOKUP function which looks for a value in the first column of a range, then returns the value in the same row, but in the column you specify.

It also supports approximate matches which is great for your needs. If your lookup table is sorted by time (ascending)VLOOKUP will find the largest match that is less than or equal to the lookup value.

=VLOOKUP(search_key, range, index, [is_sorted])


All variations assume the following:

  1. D2:E7 is the range containing your lookup table (rate sheet):
    • D2:D7 contains start times (column 1 of the range)
    • E2:E7 has the rate for each start time (column 2 of the range)
  2. A2:A contains the time(s) you want to check
  3. B2:B contains the formula result(s).
A B    C     D  E     F                           
1 Time Cost     Increment Rate 
2 3:35 $ 6.00 2:36 3
3 3:00 $ 3.00 3:06 6
4 3:45 $ 9.00 3:36 9
5 4:06 12
6 4:36 15
7 5:06 18

  1. Basic Formula

This is the kind of formula that you copy down into other rows, so the range is absolute to stop it from changing when copied: $D$2:$E$7 vs. D2:E7

=VLOOKUP(A2, $D$2:$E$7, 2)


The ARRAYFORMULA function enables a single VLOOKUP formula to return the results for multiple rows. The search_key argument is changed to the range A2:A4


  3. IFNA

If you wrap the formula in the IFNA function, you don't need to worry about blank rows and can also use an infinite range such as A2:A. Any new rows of times you add would be automatically calculated:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.