📎 This article includes 1 downloadable practice file ↓
Advertisement
The April-March financial year is the most common source of messy date formulas in Indian workbooks. Wrap the logic once.
FYSTART =LAMBDA(d, YEAR(d) - (MONTH(d) < 4))
FYLABEL =LAMBDA(d, LET(y, FYSTART(d), "FY " & y & "-" & RIGHT(y + 1, 2)))
FYQTR =LAMBDA(d, "Q" & INT(MOD(MONTH(d) - 4, 12) / 3) + 1)
FYEND =LAMBDA(d, DATE(FYSTART(d) + 1, 3, 31))
DAYSLEFT =LAMBDA(d, FYEND(d) - d)
AGEYEARS =LAMBDA(dob, asof, DATEDIF(dob, asof, "y"))| Date | FYLABEL | FYQTR |
|---|---|---|
| 12 May 2026 | FY 2026-27 | Q1 |
| 20 Jan 2026 | FY 2025-26 | Q4 |
| 31 Mar 2026 | FY 2025-26 | Q4 |
💡 For a company with a different year (e.g. October-September), add a start-month parameter:
=LAMBDA(d,[m],LET(s,IF(ISOMITTED(m),4,m),YEAR(d)-(MONTH(d)<s))). Optional parameters are lesson 7.Common mistakes
- Text dates give #VALUE!. Clean them first (Bank Statement course, lesson 2).
- Calendar quarters from PivotTables (Jan-Mar = Q1) don’t match FYQTR; group by an FY Quarter column instead.
Practice
Download the workbook below. The tasks call each function inline, like =LAMBDA(x,x*2)(A2), so the file works on any Microsoft 365 PC; in your own files, save them by name in Name Manager. The Check column turns green when you’re right.
📎 Practice files for this article
- 📗Lesson 4 practice workbook6 FY date helpers to build and check.⬇ XLSX · 14 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Advertisement
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong