How to find or get quarter from a given date in Excel?
Find and get quarter from date by formula
Get and find quarter from given date by Kutools for Excel
Convert date to quarter format by Kutools for Excel
VBA Code - Automatically extract quarter from a date column
Find and get quarter from date by formula
To find the quarter from a given date without any add-ins, you can make use of built-in Excel formulas. This method is quick and convenient, especially for users who need a direct and simple way to get the quarter from dates listed in a worksheet.
Select a blank cell next to your date column—for example, cell C1. Enter the following formula to calculate the quarter for the date in cell A1:
=ROUNDUP(MONTH(A1)/3,0)Press Enter to confirm and see the quarter number corresponding to the date.
Tip: A1 is the reference cell containing the date you wish to analyze. Adjust the cell reference as needed for your dataset.
To fill down the formula for more dates, drag the autofill handle in the bottom-right corner of the formula cell down through your list.
Note and Tips:
- You can customize the output so it reads "Quarter1", "Quarter2", etc. by using:
="Quarter"&ROUNDUP(MONTH(A1)/3,0)This formula will append the word "Quarter" before the quarter number, which may be more suitable for reporting or presentation purposes.
- If your dates are not recognized correctly, check the cell's formatting to make sure it is set as Date. Otherwise, formulas may give unexpected results.
- Remember to update the formula references if your dates start from a different cell or column (not just A1).
- If you get an error, verify that the date values are valid and not text strings. Use
DATEVALUE()if necessary to convert text dates to serial numbers.
Get and find quarter from given date by Kutools for Excel
If you prefer not to use formulas, the Convert date to quarter feature in Kutools for Excel offers a streamlined way to extract quarters from dates with minimal manual input. This method is ideal for users who want a guided interface, batch processing abilities, or to avoid formula syntax errors.
After installing Kutools for Excel, please proceed as below:（Free Download Kutools for Excel Now!)
1. Choose the cell where you want the result to appear, then navigate to Kutools > Formula Helper > Date & Time > Convert date to quarter.
2. Under the Formulas Helper dialog, select the date cell using the browse button or directly input a date in "m/d/yyyy" format. Click OK to confirm.
Once the quarter for the selected date is calculated, you can drag the autofill handle over other cells to apply the formula to all your data as needed.
Advantages: Kutools provides a user-friendly approach, supports batch processing, and reduces formula entry errors. Not only does this save time, it also allows you to perform the conversion across large datasets without manual adjustments.
Convert date to quarter format by Kutools for Excel
If you already have Kutools for Excel installed, the Apply Date Formatting tool allows you to format your dates in a variety of ways. This is useful if you need to display your dates as quarters, months, or years for readability or reporting purposes.
1. Before beginning, it is recommended to save a backup of your data. Then, select the range of dates you want to format and go to Kutools > Format > Apply Date Formatting.See screenshot:
2. In the Apply Date Formatting dialog box, choose your desired format from the Date formatting list. You can preview the changes in real time.
3. Click OK and the selected dates will be updated according to the format you chose (such as converting each date to show the month, quarter, or year only).
| Original Date | Convert to Date | Convert to Month | Convert to Year | |
 | |  |  |  |
VBA Code - Automatically extract the quarter from a date column
For situations where you have a large dataset and need to batch process quarters without manually entering formulas or relying on add-ins, Excel VBA allows you to automate this process. This solution is suited for users who are comfortable with basic macros and need to extract quarters for many rows at once, such as preparing quarterly sales summaries or data reviews.
- Click Developer Tools > Visual Basic. In the opened Microsoft Visual Basic for Applications window, click Insert > Module.
- Copy and paste the following code into the Module:
Sub ExtractQuarterFromDates()
Dim rng As Range
Dim cell As Range
Dim targetColumn As Integer
Dim lastRow As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the date range:", xTitleId, rng.Address, Type:=8)
targetColumn = rng.Columns(1).Column + 1 ' Next column for result
lastRow = rng.Rows.Count + rng.Row - 1
For Each cell In rng
If IsDate(cell.Value) Then
Cells(cell.Row, targetColumn).Value = "Q" & Application.WorksheetFunction.RoundUp(Month(cell.Value) / 3, 0)
Else
Cells(cell.Row, targetColumn).Value = ""
End If
Next cell
End Sub- To run the macro, click the
button. A prompt will let you select your column of dates. The script will then write the corresponding quarter result in the next column (e.g., if your dates are in column A, the quarters will appear in column B).
button. A prompt will let you select your column of dates. The script will then write the corresponding quarter result in the next column (e.g., if your dates are in column A, the quarters will appear in column B).Relative Articles:
- How to convert date to fiscal year/quarter/month in Excel?
- How to quickly split date into separate day, month and year in Excel?
- How to convert between date and Unix timestamp in Excel?
- How to convert between Julian date and calendar date in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in