How do I write an IF(VAT@20% // VAT@5%) code for hotels

[Peter_Boothby]

Any ideas how to write an IF code for hotel VAT?

I have 50 hotel nights showing in column {Nights} multiplied by the {nightly rate} @ £200/night = £10,000

VAT is calculated @ 20% on top of the first 28 nights and then @ 5% from the 29th night onwards.

I would love a formula that can calculate this.

[kuovonne · Accepted solution]

Welcome to the Airtable community!

Does this work for you?

SUM(
  (
    IF("VAT rate for first 28 nights", 0.2) 
    * IF({Nights} <= 28, {Nights}, 28) 
    * {nightly rate}
  ),
  (
    IF("VAT rate for nights 29+", 0.05)
    * IF({Nights} > 28, {Nights} - 28, 0) 
    * {nightly rate}
  )
)

[Peter_Boothby]

Oh wow – that worked! Amazing! Thank you so much!