Calculate Quarterly Dates in Excel: Formulas and Examples

Formulas & functions · Updated November 19, 2026

To calculate a calendar quarter’s first day in Excel, put a date in A2 and enter =DATE(YEAR(A2),3*INT((MONTH(A2)-1)/3)+1,1); for May 17, 2026, the result is April 1, 2026. To find that quarter’s last day instead, use =EOMONTH(DATE(YEAR(A2),3*INT((MONTH(A2)-1)/3)+1,1),2), which returns June 30, 2026.

What quarterly dates mean in Excel

A calendar year is divided into four three-month quarters: Q1 is January through March, Q2 is April through June, Q3 is July through September, and Q4 is October through December. A quarterly date calculation usually means finding a quarter’s beginning or ending date from a date that falls within it. For example, a transaction dated May 17 belongs to Q2, whose first and last dates are April 1 and June 30.

Excel stores dates as serial numbers, so formulas can calculate with them rather than treating them as text. A formula may return the correct date but display it as a number until you apply a date format. The examples here use calendar quarters and Microsoft Excel for Microsoft 365 on Windows. Fiscal quarters, which follow a company’s accounting calendar, can have different boundaries.

Calculate the first day of a calendar quarter

With the source date in A2, use the following formula to return the first day of its calendar quarter:

=DATE(YEAR(A2),3*INT((MONTH(A2)-1)/3)+1,1)

For A2 equal to May 17, 2026, the formula returns April 1, 2026. It takes the year from the source date, groups its month into a set of three, and sets the day to 1. The arithmetic works because January, April, July, and October are months 1, 4, 7, and 10. The formula handles dates in all four quarters without needing separate conditions.

To fill the calculation down a worksheet, enter the formula beside the first date and copy it to the rows below. Excel will adjust A2 to A3, A4, and so on. If your source dates are in a structured Excel table column named Date, you can also use the column reference [@Date] in place of A2; Excel will generally fill the calculated column automatically.

Calculate the last day of a quarter

With the date to classify in A2, this formula returns the last day of the same calendar quarter:

=EOMONTH(DATE(YEAR(A2),3*INT((MONTH(A2)-1)/3)+1,1),2)

For May 17, 2026, it returns June 30, 2026. The inner DATE expression finds the quarter’s first day, and EOMONTH moves two months forward to the final day of the third month. That automatically accounts for months of different lengths and leap years. For example, a date in February 2024 belongs to Q1, which ends March 31, 2024.

You can also calculate a quarter end directly from the source date. With the date in A2, enter =EOMONTH(A2,2-MOD(MONTH(A2)-1,3)). For May 17, 2026, this also returns June 30, 2026. The earlier version is often easier to audit because it visibly builds on the quarter-start calculation; the shorter one calculates how many months remain until the quarter’s final month.

Return a quarter number or label

If you need the quarter number for grouping or reporting rather than a boundary date, put the date in A2 and use =ROUNDUP(MONTH(A2)/3,0). The result for May 17, 2026, is 2. This formula divides the month number by three and rounds up, so months 1–3 return 1, months 4–6 return 2, and so forth.

To show a readable label, with the date still in A2, use ="Q"&ROUNDUP(MONTH(A2)/3,0)&" "&YEAR(A2). For the example date, the displayed result is Q2 2026. A label is convenient in a chart or summary, but it is text, not a date. Keep an actual date value in your data if you need to sort chronologically, calculate durations, or apply date-based filters. In particular, text labels can sort alphabetically rather than in calendar order.

Work with fiscal quarters

A fiscal year may begin in a month other than January. If an organization’s fiscal year begins in July, for example, July through September is its first fiscal quarter, and April through June is its fourth. The calendar-quarter formulas above will not match that schedule. Enter the fiscal year’s starting month as a number from 1 to 12 in B1; use 7 for July.

With the transaction date in A2 and the fiscal start month in B1, calculate its fiscal quarter number with =INT(MOD(MONTH(A2)-$B$1,12)/3)+1. For May 17, 2026, and B1 equal to 7, the result is 4: May falls in the fourth quarter of a fiscal year that began the previous July. The dollar signs keep the B1 reference fixed when you copy the formula down a list.

To return the first day of that fiscal quarter, with the same A2 and B1 setup, use =EDATE(DATE(YEAR(A2),$B$1,1),3*INT(MOD(MONTH(A2)-$B$1,12)/3)-IF(MONTH(A2)<$B$1,12,0)). For the May 17, 2026 example with a July start, the result is April 1, 2026. EDATE moves the fiscal-year anchor by the required number of months; the IF adjustment accounts for dates before the fiscal start month, which belong to the fiscal year that began in the prior calendar year. Confirm the organization’s fiscal-year naming convention separately: some organizations name a fiscal year for the year in which it starts, while others use the year in which it ends.

Generate a list of quarterly dates

If you want a series of quarter starts rather than a result for each transaction, first calculate a quarter-start date and use it as the starting point. With the first quarter start in A2 and the number of dates to generate in B2, enter =EDATE(A2,3*SEQUENCE(B2,1,0,1)). If A2 is January 1, 2026, and B2 is 4, the spilled results are January 1, April 1, July 1, and October 1, 2026. SEQUENCE creates the offsets 0, 1, 2, and 3, and EDATE advances by three months for each offset.

SEQUENCE is available in Microsoft 365 and Excel 2021 or later. In versions without dynamic arrays, enter the starting date in A2, then in A3 use =EDATE(A2,3) and copy the formula down. With A2 equal to January 1, 2026, the next result is April 1, 2026; continuing the formula produces July 1 and October 1. This manual series is useful when you need a fixed number of rows or want to inspect each step.

Format and check the results

If a result appears as a number such as 46113, the calculation may still be correct: Excel is showing the date’s underlying serial value. Select the result cells and choose Home > Number Format, then select Short Date or Long Date. You can also open Format Cells with Ctrl+1 on Windows. On Mac, Command+1 opens Format Cells. The exact date appearance depends on the workbook’s regional settings, but a properly formatted Excel date remains usable in calculations.

For a quick check, compare a few results with known quarter boundaries. Dates in March should produce a Q1 end of March 31; dates in June should produce June 30; and dates in December should produce December 31. Test dates around a boundary as well: March 31 belongs to Q1, while April 1 belongs to Q2. Including the year matters because quarter-end formulas should preserve it, including when the date falls in a leap year.

Common problems and how to avoid them

  • The source value is text, not a date. A cell may look like a date but be stored as text, particularly after importing a CSV or copying data from another system. If formulas return errors or unexpected results, check whether Excel recognizes the value as a date. Re-entering it in a recognized date format or converting the imported column may be necessary.
  • The workbook uses semicolons in formulas. The formulas here use commas as argument separators. Excel’s separator can vary with regional settings. If Excel rejects a formula, your installation may require semicolons instead; use the separator Excel expects rather than changing the calculation itself.
  • A fiscal calendar is mistaken for a calendar year. A formula based on January-to-December quarters will be wrong for a business whose fiscal year starts in another month. Set the fiscal start month deliberately and verify the company’s quarter definitions, particularly if it uses a 4-4-5 calendar or another nonstandard schedule.
  • A result is a label instead of a date. A value such as Q2 2026 is useful for display, but it cannot replace an actual date in date arithmetic. Keep the underlying quarter start or end date in a separate column and create labels only for presentation.
  • Dates are being sorted unexpectedly. Check that the cells contain real Excel dates and not text labels. Real dates sort chronologically by their stored values. If a table includes multiple years, include the year in labels or sort by the underlying date so that Q1 from different years is not grouped as though it were the same period.

Choose the right calculation

Use a quarter-start or quarter-end formula when you need period boundaries for filtering, summaries, or date comparisons. Use a quarter number when you need a compact grouping field, and a text label when the main goal is a readable chart heading. For a reusable reporting calendar, generate quarter starts as real dates and derive labels separately. This keeps calculations dependable while allowing the display format to change.

For a one-off report, a formula is usually more maintainable than typing quarter dates manually: changing the source date updates the result, and the same formula can be filled down. For an organization with an unusual fiscal calendar, first document the actual period rules; a simple three-month formula may not reflect a 4-4-5 calendar, a 52/53-week year, or other accounting conventions.

Frequently Asked Questions

How do I get the quarter number from a date in Excel?
With the date in A2, use =ROUNDUP(MONTH(A2)/3,0). It returns 1 for January through March, 2 for April through June, 3 for July through September, and 4 for October through December.

How do I calculate the next quarter date?
If A2 contains a quarter’s first day, use =EDATE(A2,3) to return the next quarter’s first day. For example, January 1, 2026 advances to April 1, 2026. This assumes A2 is already a quarter start.

How do I find the quarter end date from any date?
With the date in A2, use =EOMONTH(DATE(YEAR(A2),3*INT((MONTH(A2)-1)/3)+1,1),2). It finds the quarter containing the date and returns the last calendar day of that quarter.

How can I calculate quarters when the fiscal year starts in July?
Use a fiscal-start-month value of 7 and the fiscal quarter formula described above. Calendar-quarter formulas assume January is the first month, so they do not correctly label July-to-June fiscal periods.

Why does Excel show a number instead of a date?
Excel stores dates as serial numbers. Apply a date display format through Home > Number Format or Format Cells; formatting changes how the value appears, not the date calculation itself.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.