📎 This article includes 1 downloadable practice file ↓
Dates in business are rarely “plus 30 days”. Payment terms say “end of next month”, delivery skips Sundays and Gandhi Jayanti, and the financial year starts in April. Excel has a function for each.
Due dates
| Rule | Formula |
|---|---|
| 30 days after invoice | =B2+30 |
| End of next month | =EOMONTH(B2,1) |
| Same date next quarter | =EDATE(B2,3) |
| First day of the month | =EOMONTH(B2,-1)+1 |
Working days
=WORKDAY(order_date, 7) ' 7 working days later, skipping Sat & Sun
=WORKDAY(order_date, 7, Holidays) ' also skipping listed holidays
=WORKDAY.INTL(order_date, 7, 11, Holidays) ' only Sunday off (weekend code 11)
=NETWORKDAYS(DATE(2026,10,1), DATE(2026,10,31)) ' working days in OctoberKeep holidays in a small table and name it Holidays. Update it once a year.
Indian financial year
FY start year: =YEAR(B2)-(MONTH(B2)<4)
FY label: ="FY "&(YEAR(B2)-(MONTH(B2)<4))&"-"&RIGHT(YEAR(B2)-(MONTH(B2)<4)+1,2)
FY quarter: ="Q"&ROUNDUP(MOD(MONTH(B2)-4,12)/3+0.01,0)
FY month no.: =MOD(MONTH(B2)-4,12)+1 ' April = 1 ... March = 124 October 2026 is in Q3 of FY 2026-27.
(MONTH(B2)<4) is TRUE (1) for January-March, so subtracting it moves those months into the previous FY.Common mistakes
- Dates stored as text (“04.10.2026”) give #VALUE! everywhere. Convert with Text to Columns (DMY) or DATEVALUE.
- Result shows 46329. That’s a date serial number; format the cell as a date (Ctrl+1).
- Holiday list includes weekends. Harmless, but don’t expect them to reduce the count twice.
Practice
Download this lesson’s workbook below. Format answer cells as dates where the answer is a date. Type your formula in the yellow column; the Check column turns green when the result matches, and the Answers sheet has a working formula for every task.
📎 Practice files for this article
- 📗Lesson 6 practice workbookInvoice, order and joining dates plus a holiday list, 8 tasks.⬇ XLSX · 13 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.
Stuck on a step? Ask a question and the AI answers using this article.