Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Extract all partial matches in Excel
  • How to get first row number in range in Excel
  • How to get last row in numeric data in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Count rows with at least n matching values

Data Analysis Examples

  • How to create Gauge Chart in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • How to Sort by Color in Excel
  • How to create a Histogram in Excel
  • How To Create Frequency Distribution in Excel

Data Validation Examples

  • Excel Data validation with conditional list
  • Excel Data validation whole percentage only
  • Excel Data validation no punctuation
  • Excel Data validation only dates between
  • Excel Data validation allow weekday only

How to calculate quarter from date in Excel

by

If you want to calculate the quarter (i.e. 1,2,3,4) from a date, you can use the ROUNDUP function together with MONTH.

Formula

=ROUNDUP(MONTH(date)/3,0)

Explanation

In the example shown, the formula in cell C5 is:

Worked Example:   Find, Trace and Correct Errors in Excel Formulas using 'Formula Auditing'
=ROUNDUP(MONTH(B5)/3,0)

How this formula works

In this case, the formula first extracts the month as a number between 1-12, then divides this number by 3. The result is then rounded up to the nearest whole number using the ROUNDUP function.

Worked Example:   Round a number up in Excel

The ROUNDUP function works like the ROUND function, except that ROUNDUP will always round numbers 1-9 upwards to a given number of digits.

Worked Example:   Round numbers in Excel

Adding a Q

If you want the quarter number to include a “Q” you can concatenate. The formula in D5 is:

="Q"&ROUNDUP(MONTH(B5)/3,0)

The result is the letter “Q” prepended to the quarter number:

Post navigation

Previous Post:

How to get same date next month or previous month in Excel

Next Post:

How to calculate project start date based on end date in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to count specific characters in a cell in Excel
  • REPLACE, REPLACEB functions: Description, Usage, Syntax, Examples and Explanation
  • How to count specific characters in a range in Excel
  • How to strip protocol and trailing slash from URL in Excel
  • UNICHAR function: Description, Usage, Syntax, Examples and Explanation
  • How to join date and text together in Excel
  • Add days exclude certain days of week in Excel
  • Calculate total hours that fall between two times in Excel
  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • Add decimal hours to time in Excel
  • Calculate interest for given period in Excel
  • Tax rate calculation with fixed base in Excel
  • How to calculate compound interest in Excel
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • FV function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles