Ravindra BagaleCourses & study guides Track your progress
Hard challenge

Hard: India fiscal year vs calendar YEAR()

By · Free ·

Absolute refsNumbers as textLeading spacesFilter left onChart range% vs decimalDuplicate headerROUND vs displayFreeze panesPaste valuesVLOOKUP & textVLOOKUP col indexHLOOKUP TRUESUMIF spacesCOUNTIFS ORIF nest orderDate serialINDEX MATCHWildcard COUNTIFMixed currencyCircular & datesCircular interest3D sum renameNamed range staleCF vs IFPivot staleHard-coded NPVValidation movedPQ step orderTyped-over formulaSUM wrong anchorsFiscal year Apr–MarFind all bugsInterview oral

Column C uses =YEAR(date). Mar-2025 becomes 2025 but India FY-start is 2024. Fixed IF(MONTH≥4,YEAR,YEAR−1) → 2024,2025,2025,2025,2026 (LibreOffice verified).

About this data: Made-up invoices for Priya Nair (Kochi) and Omar Hassan (Cairo). Practice only.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Indian finance reports on Apr–Mar. Blind YEAR() breaks dashboards near the FY boundary.

What: Broken C5=2025. Fixed C5=2024.

How:

  1. Open FY.
  2. Check INV-01 (15 Mar 2025).
  3. Replace YEAR with IF(MONTH≥4…).
  4. Confirm the expected list.

Ravindra Bagale’s Tip

Document the FY rule on the sheet: “FY starts April — months 1–3 belong to previous start year.”

Bugs explained (with mini examples)

Bug 1 — Calendar YEAR()

Ignores Apr–Mar boundary.

Bug 2 — No documented FY rule

Juniors guess US calendar year.

Practice tasks (12)

  1. Task 1. Broken C5 for Mar 2025?
    Hint: 2025.
  2. Task 2. Fixed C5?
    Hint: 2024.
  3. Task 3. Write the formula pattern.
    Hint: =IF(MONTH(B5)>=4,YEAR(B5),YEAR(B5)-1)
  4. Task 4. Apr 2025 FY-start?
    Hint: 2025.
  5. Task 5. Feb 2026 FY-start?
    Hint: 2025.
  6. Task 6. Apr 2026 FY-start?
    Hint: 2026.
  7. Task 7. Interview sentence.
    Hint: India FY is Apr–Mar, not Jan–Dec.
  8. Task 8. Sheet name?
    Hint: FY.
  9. Task 9. Why MONTH>=4?
    Hint: April starts the FY.
  10. Task 10. US calendar same as India FY?
    Hint: No.
  11. Task 11. Label FY as FY24-25?
    Hint: Optional follow-up.
  12. Task 12. Save fixed.
    Hint: Yes.

In-browser formula check

Type the fixed formula you would put in the named cell. We check formula text — open the workbook for full recalculation.

Step-by-step solution (try first)
  1. Replace C5:C9 with =IF(MONTH(B5)>=4,YEAR(B5),YEAR(B5)-1) (relative).
  2. LO: C5 2025 → 2024; list 2024,2025,2025,2025,2026.

Quick quiz

Q1. India FY typically starts:
Q2. Broken Mar-2025 tag:
Q3. Fixed Mar-2025 FY-start:
Q4. Helper function pair:
Q5. Apr-2026 FY-start: