 # 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

=E3-D3

=SUM(D4-C4)*24

=MOD(D6-C6,1)*24

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

=IF(F3>=2,50,IF(F3>=1.5,25,IF(F3>=1,20,IF(F3>=0.5,16,12))))

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

• Don't you need to set the format to time in Column F instead of number?

Then just an IF fucntion based on the time charged?

• 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:

=(E3-D3)*24

Rory
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.

• Depending on your XL version, solution attachedusing XLOOKUP

Do NOT use merged cells, they are nothing but trouble

Avoid manual alignment in columns as they mask if data is text ( left-aligned) or not

Billing.Patrons.xlsx

• 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.