Spreadsheet Practice

How to count dates in a fiscal year in Excel

Formula (Excel and Google Sheets)

=COUNTIFS(B2:B12,">="&DATE(G1-1,G2,1),B2:B12,"<"&DATE(G1,G2,1))

Count from the fiscal year's first day up to the next one's: =COUNTIFS(B2:B12,">="&DATE(G1-1,G2,1),B2:B12,"<"&DATE(G1,G2,1)), with the fiscal year in G1 and its start month in G2.

Result on the example
7 in G3
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in G3. Change any input and the result updates, or edit the formula to experiment.

How it works

Fiscal year 2026 with a July start runs from July 1, 2025 to June 30, 2026. DATE(G1-1,G2,1) builds the first day, DATE(G1,G2,1) the first day of the next fiscal year, and COUNTIFS counts the dates from one up to, but not including, the other.

This names the fiscal year after the calendar year it ends in, the most common convention. June 30 and July 1 sit on either side of the boundary.

=COUNTIFS(B2:B12,">="&DATE(G1-1,G2,1),B2:B12,"<"&DATE(G1,G2,1))

Result7

  1. B2:B1211 rows × 1 col: 6/30/2025, 7/1/2025, 9/15/2025, 12/31/2025, …

    A range: 11 cells in one column.

  2. G12026

    The value in G1.

  3. G1-12025

    The - operator subtracts.

  4. G27

    The value in G2.

  5. DATE(G1-1,G2,1)7/1/2025 (45839)

    DATE: Builds a date from parts.

    • yearG1-12025
    • monthG27
    • day1
  6. ">="&DATE(G1-1,G2,1)">=45839"

    The & operator joins as text.

  7. DATE(G1,G2,1)7/1/2026 (46204)

    DATE: Builds a date from parts.

    • yearG12026
    • monthG27
    • day1
  8. "<"&DATE(G1,G2,1)"<46204"

    The & operator joins as text.

  9. COUNTIFS(B2:B12,">="&DATE(G1-1,G2,1),B2:B12,"<"&DATE(G1,G2,1))7

    COUNTIFS: Counts rows that meet every condition.

    • criteria_range1B2:B1211 rows × 1 col: 6/30/2025, 7/1/2025, 9/15/2025, 12/31/2025, …
    • criteria1">="&DATE(G1-1,G2,1)">=45839"
    • criteria_range2B2:B1211 rows × 1 col: 6/30/2025, 7/1/2025, 9/15/2025, 12/31/2025, …
    • criteria2"<"&DATE(G1,G2,1)"<46204"

In Google Sheets

The same formula works in Google Sheets: =COUNTIFS(B2:B12,">="&DATE(G1-1,G2,1),B2:B12,"<"&DATE(G1,G2,1)). We ran it in Google Sheets mode on the same data and it returns 7.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMPRODUCT(--(YEAR(B2:B12)+(MONTH(B2:B12)>=G2)=G1))7Works out each date's fiscal year: the calendar year, plus 1 from the start month on. The same expression makes a fiscal-year helper column.

Common mistakes

  • If your company names the fiscal year after the year it starts in, use DATE(G1,G2,1) and DATE(G1+1,G2,1) instead.
  • A fiscal year that starts in January is just the calendar year; see the page on counting dates in a given year.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Count dates in a fiscal year

In G3, count the invoices in fiscal year G1, which starts in the month number in G2.

Questions

How do I count by fiscal quarter?

Shift the date back to a January-based year first: the fiscal quarter is =ROUNDUP(MONTH(EDATE(B2,1-G2))/3,0).

The US federal fiscal year starts in October. What do I change?

Set G2 to 10: fiscal year 2026 then runs from October 1, 2025 to September 30, 2026.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.