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

Billing.Patrons.xlsx.

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.

Quote from Antony Lambert

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.

Display More

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.

Quote from Pecoflyer

Depending on your XL version, solution attachedusing XLOOKUP

Please add your XL version to your profile.

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

Display More

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