How to calculate time for billable increments

  • Thank you for taking the time to review and maybe assisting in this request.

    I need some help.

    I'm trying to calculate the time to bill customers from when they enter to when they leave based on set billing increments

    0.5 hours = $12

    1.0 hours = $16

    1.5 hours = $20

    2.0 hours = $25

    A Day Pass = $50

    In the sheet attached I've tried the following formulas




    I'm not getting the results I'm expecting. As a result the incremental billing formula


    is not working either.

    I'm obviously doing something wrong here and missing what it is.

    Any help I can get would be greatly appreciated


  • Formatting seems to have no effect or difference in the result. I think it's more that I'm going about the calculations in the wrong manner. I don't know. I've tried for days now to figure out what I'm missing or doing wrong, but I'm blind to what I don't know.

  • Since times are fractions of 1 day, you need to multiply by 24 to get the number of hours:


    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • This in Cell G3 seems to do the trick.

    IF(F3>1.99,50,IF(AND(F3>1.49,F3<1.99),20,IF(AND(F3>0.99,F3<1.49),16,IF(AND(F3>0.49,F3<0.99),12)))) and then pulled down to G9.

    This in F3 pulled down with colum F formatted as number. =(E3-D3)*24

    I assumed that 2 hours exactly made the client liable for the next tier. you could amend that I'm sure.

  • Thanks. I needed to tweak it a bit to get the right results, but it works as I needed it to. Now i need to search through ozgrid for the formula to automatically continue these formulas downward.

  • Thanks. It's been a few years since I've been on OzGrid.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!