Excel Date Formulas Cheat Sheet: 25 Formulas You Will Actually Use

Excel Date Formulas Cheat Sheet: 25 Formulas You Will Actually Use

📎 This article includes 1 downloadable practice file ↓

⏱ 3 min readUpdated 5 October 2026

Bookmark this page. These are the date formulas that come up again and again in reports, HR sheets and finance work. A2 holds a date unless stated.

In this article
  1. Today and the current month
  2. Adding and subtracting
  3. Age, tenure, differences
  4. Parts and labels
  5. Indian financial year
  6. Checks
  7. Continue learning

Today and the current month

1  =TODAY()                         ' today
2  =EOMONTH(TODAY(), 0)             ' last day of this month
3  =EOMONTH(TODAY(), -1) + 1        ' first day of this month
4  =EOMONTH(TODAY(), -2) + 1        ' first day of last month
5  =EOMONTH(TODAY(), -1)            ' last day of last month

Adding and subtracting

6  =A2 + 30                         ' 30 days later (due date)
7  =EDATE(A2, 1)                    ' same day next month
8  =EDATE(A2, -12)                  ' one year earlier
9  =WORKDAY(A2, 10, Holidays)       ' 10 working days later
10 =B2 - A2                         ' days between (format as Number)

Age, tenure, differences

11 =DATEDIF(A2, TODAY(), "y")                         ' completed years
12 =DATEDIF(A2, TODAY(), "ym")                        ' extra months
13 =DATEDIF(A2, TODAY(), "y") & "y " & DATEDIF(A2, TODAY(), "ym") & "m"
14 =NETWORKDAYS(A2, B2, Holidays)                     ' working days between
15 =YEARFRAC(A2, B2)                                  ' years as a decimal

Parts and labels

16 =TEXT(A2, "dddd")                ' Sunday
17 =TEXT(A2, "mmm-yy")              ' Oct-26
18 =WEEKDAY(A2, 2)                  ' 1 = Monday … 7 = Sunday
19 =ISOWEEKNUM(A2)                  ' week number
20 =ROUNDUP(MONTH(A2)/3, 0)         ' calendar quarter

Indian financial year

21 =YEAR(A2) - (MONTH(A2) < 4)                                        ' FY start year
22 ="FY " & (YEAR(A2)-(MONTH(A2)<4)) & "-" & RIGHT(YEAR(A2)-(MONTH(A2)<4)+1, 2)   ' FY 2026-27
23 ="Q" & INT(MOD(MONTH(A2) - 4, 12) / 3) + 1                       ' FY quarter (Apr-Jun = Q1)

Checks

24 =ISNUMBER(A2)                    ' TRUE for a real date, FALSE for text dates
25 =AND(A2 >= DATE(2026,4,1), A2 <= DATE(2027,3,31))   ' inside FY 2026-27?
💡 If a date formula returns a number like 46299, it’s right; format the cell as a date. If it returns #VALUE!, the ‘date’ is probably text: convert with DATEVALUE or Text to Columns.

Go deeper: the dates lesson and working days with holidays.

Advertisement
⚡ Interactive Practice Lab & Formula Tester:

Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.

Continue learning

All Excel Formulas guides →
Dependent Dropdown Lists in Excel (Two Methods That Actually Work)Create a second dropdown that changes with the first — Category then Product, Country…Why 0.1 + 0.2 Is Not Exactly 0.3 (and Why Excel Sometimes Agrees)Computers store decimals in binary, so 0.1 + 0.2 becomes 0.30000000000000004. Why it happens,…Excel’s Strangest Behaviours Explained: 15 “Bugs” That Are Actually FeaturesWhy Excel turns 1-2 into a date, drops leading zeros, shows ####, changes long…Why Does Excel Have Exactly 1,048,576 Rows? The Logic Behind Software LimitsWhy Excel stops at 1,048,576 rows and column XFD, why old Excel had 65,536…

📎 Practice files for this article

  • 📗
    Date scenarios workbookMonth maths, financial-year start, working days with holidays — with PASS/FAIL checks and the 2 most common date mistakes.
    ⬇ XLSX · 8 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.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong