[Solved] Tax Calculation

[Maps]

Hi Shamimarita.
Your formula works fine in Ofice 365. Thanks.
Besides the LET formula what other formula can I use as the the latter does not seem to work on older versions of Office?

 

[shamimarita]

Hello Maps,

I’m glad the formula is working correctly in Office 365. Since the LET function is not available in older versions of Excel, you can use the same calculation by replacing the variable names with their actual cell references:

=ROUND(IF(DATEDIF(C21,C23,"Y")<60,8200,IF(DATEDIF(C21,C23,"Y")<75,10900,12100))/(D31-C31+1)*MAX(0,C23-MAX(C31,C24)+1),2)

This formula follows the same logic as the previous LET formula:

• C21 = Date of Birth
• C23 = Pay Date
• C24 = Employee Engagement/Start Date
• C31 = Tax Year Start Date
• D31 = Tax Year End Date

The MAX(C31,C24) part ensures that the rebate calculation starts from the later of the tax-year start date or the employee’s engagement date. Therefore, if the tax year starts on 1 July but the employee starts on 1 August, no rebate will be calculated for July.

This version should work in older Excel versions that support DATEDIF, IF, and ROUND.

 

[Maps]

Hi Shamimarita.
Thank you so much.
You have been helpful a lot.

 

[shamimarita]

Hello Maps,

You are most welcome. Thanks for your feedback and appreciation, it really means a lot to me.