income tax return period calculation

[esaji]

I am trying to calculate the income tax return period from my bank statement's unformatted dates, my computer is on the following time-zone UTC+3:00 Kuwait time zone and what I am trying to work on is Pakistan related date but there is no time in the statement dates values.

the income tax return time period always starts from the following date ranges

1st of july of the previous year to june 30th of next year - this range is marked as tax year in FBR , What I need to do file income tax return based on this range for every year

01-Jul-2021 - 30-Jun-2022
01-Jul-2022 - 30-Jun-2023
01-Jul-2023- 30-Jun-2024

so far I formated the date from column A of the statement using the Datevalue formula , also date in my computer format is dd-mm-yy , i applied the same for formated date column in B
next I used the following formula on formated date in column to give me Tax period years range



formula that I am using in C column is

Please Login or Register  to view this content.

and in column E are

Please Login or Register  to view this content.

80% of the results are coming correct but the main issue is coming in the following dates where tax period is wrongly adding month of june 2023 in next year tax period this is what I am trying to fix and a similar issue is coming in the second formua where current year , previous year and next tax period are sometime came wrong

kindly review the sheet and suggest what should Ido to make sure I get correct results?

[AliGW]

Are you still using Excel 2016?

[esaji]

now I am using office 2019

[JeteMc]

Instead of complex formulas I suggest producing a second table with columns for Start Date, Tax Year and Period.
You could then use the following formulas:
For the Income Tax column: =INDEX(Table1[Tax Year],MATCH([@[date formated]],Table1[Start Date]))
For the Period column: =INDEX(Table1[Period],MATCH([@[date formated]],Table1[Start Date]))
Let us know if you have any questios.