You might need to summarize your data by time periods for an Excel project. The quarter is one such range of dates. There are four quarters in a year since a quarter is three months long. These quarters correspond to the regular calendar year. The first quarter covers the entire period from January 1 to March 31.
How to find or calculate quarter from a given date in excel? Or Calculate or Find quarter from date by formula.
Step-1: Prepare a data table with information outlined below in first table
Step-2: Calculate the Quarter number in the next table Cell-B2:
- Type this formula ROUNDUP(MONTH(A2)/3,0) into Cell-B2, then press Enter key to get the relative quarter
- Drag the autofill handle down to the cells to apply this formula to cell B3 to B15
*Here we use two functions, MONTH which is divided by 3 get the quarter and ROUNDUP the quarter number into the next round integer.
Step by step see how the Functions work:
Step-1: MONTH Function:
The MONTH function extracts the month from a given date as a month number between 1 to 12.
Step-2: Divided Column B2 Month value by 3 to get the quarter in Column C2
Step-3: ROUNDPU the Column C2 value to get the Quarter without fraction
Step-4: Combining all 3-function in Column F2 mentioned in Step-1 to 3
How to customize or set quarter starting month or period in a Fiscal Year using function?
Quarter target: If April is the first month of the Fiscal Year Q-1, And Jul – Sep is Q2, Oct – Dec is Q3 and Jan – Mar is Q4
Step-1: Type below formula in Cell B2 to set Fiscal Year Q-1 from April to June
- =IF(ROUNDUP(MONTH(A2)/3,0)-1=0,4,(ROUNDUP(MONTH(A2)/3,0)-1))
If July is the first month of the Fiscal Year:
Type below formula in Cell B2 to set Fiscal Year Q-1 from July to Sept
- =IF(ROUNDUP(MONTH(A7)/3,0)-1=0,4,(ROUNDUP(MONTH(A7)/3,0)-2))
