It means we are trying to find a value betw… Just the solution. NOTE: The two minus signs are a double negative, and they convert a TRUE result to a 1, and a FALSE result to a zero. Click for free full featured trial in Is there a way to make excel return 2012? Please copy the formula below: Where 10 is the fiscal year starting the month and can be changed to any month except January. This formula will work just fine: IF YOU WANT TO FIND FISCAL YEAR IN INDIA.... To calculate the FISCAL YEAR, MONTH, and QUARTER of a given date (here in A4), these formulas will work for any FY starting month (here in $D$1): In the Fiscal Year example, the formula in cell D4 should be: Would it be easier to do this [b]MONTH(A4)+(6)[/b] instead of [b]MONTH(A4)+($D$1-1)[/b] for the fiscal year calculations? Dear all, Imb, that is pretty much what I had already done, and it works, but just not quite as well as Access handles a calendar year. Change the date to fiscal year. If your fiscal year starts in July - the 7th month. 1. To convert date to fiscal month, you also need to make a table firstly. Change the starting month of your fiscal year. 300 handy Excel add-ins, free to try with no limitation in How to Group numbers in excel pivot table? Let’s assume the fiscal year runs from April to March. If you want to add years to a date in excel, you can’t just simply do an addition to add years. Figure 1- How to Group Pivot Table Data by Year. Select a cell, and type the fiscal year starting month number into it, here, my company’s fiscal year starts from July 1st, and I type 7. How do I convert a date to show the fiscal year in excel 2010? Quarter: The Quarter reflects the fiscal quarter. Creating Excel Slicers for Fiscal Years. If you need something like "2001-2002" you can use some string functions etc to prodcuce that. [range_lookup]– true or false, are we doing a range lookup? Here we will select months and years. We need to change this to a Fiscal Year. excel vba ← Back to the Posts. This step by step tutorial will assist all levels of Excel users in getting the fiscal year from a given date. The IRS allows nonprofits to file using one of two methods: calendar year and fiscal year, offering the following definitions: 1. Ask Question Asked 5 years, 2 months ago. A fiscal year is the period used for budget, accounting and financial reporting, and differs from a calendar year. In some organizations, the operationally recognized months don’t start on the 1st and end on the 30th or 31st. Firstly, you need to make a table as below screenshot shown. Source(s): convert date show fiscal year excel 2010: https://bitly.im/vHzZX. However, the grouping options are based on the calendar year, and there are no options for grouping pivot table dates by fiscal year. The fiscal year we operate under is July 1 to June 30. To compare year over year, we'll change it to a line chart. The fiscal year may vary among different countries and organizations. When I type in 4, it does not appear to populate correctly. For Example, =YEAR (2018-11-25) would return the result as 2018. Tip: In the above formula, A6 is the date cell, and the number series 7,8,9…is the fiscal month number series you type in step 1. that’s it, we are done! Kutools for Excel: with more than Update 2: I do not know what to do with the y at the end of the formula. For example, if Fiscal year start is set to April, the quarter for the date June 1, 2004 would be Q1. Convert a date to the Financial Year (Fiscal Year) like '01/01/2017' = '2018'. 10/1/2012, that did not work. Change the date to fiscal year. The year function first returns the year from the date in B5, 2017. This tutorial explains the syntax and uses of the Excel YEAR function and provides formula examples to extract year from date, convert date to month and year, calculate age from the date of birth and determine leap years.. I came up with a self contained choose() to calculate the fiscal year that doesn't need to use an external cell as an input. Essentially you create a measure that says if the month is between x and y then produce the year, if not, produce year-1. Viewed 775 times 2. In this example, the fiscal year starts in July, so January is fiscal month 7. Instead, they have specific days marking the beginning and end of a month. =YEAR (C6) Then, the IF function compares the month number for the date entered in cell C6, to the fiscal year starting month in cell C4 IF (MONTH (C6)>=C4 Here's mine for fiscal year July x+1 through June x: Fiscal Year = IF('Transactions FY11 to FY18'[Date Month]<7,'Transactions FY11 to FY18'[Date Year], 'Transactions FY11 to FY18'[Date Year]-1) Hope this helps! This tutorial explains the syntax and uses of the Excel YEAR function and provides formula examples to extract year from date, convert date to month and year, calculate age from the date of birth and determine leap years.. This can be quite helpful in many ways. To convert dates in cells to Fiscal years, you can create a formula based on the YEAR function and the MONTH function. This enables us to analyze, summarize, calculate, and visualize trends, comparisons, and patterns in our data. In a few recent posts, we have explored different ways to calculate dates and times in Excel and learned a variety of useful functions such as WEEKDAY, DAY, MONTH and WEEKNUM. For example, if Fiscal year start is set to April, the year for the date June 1, 2004 would be shown as FY 2005. Fiscal Year using Two Years (e.g., 2015 – 2016). Pure VBA without Excel formulas. For any fiscal year started other than January is considered technically custom fiscal year in Excel world. A completed date of 8/15/2007 would be FY08. Posted on Mar 7th, 2017, 11:53 AM, , User Since 230 months ago, User Post Count: 0. I believe your formula may be incorrect. In columns A and B below is the data I want to analyse in a PivotTable and Pivot Chart; it’s the trading volume of some stocks by date. It does not work unless the fiscal year begins with month 7. I wrote 3 because in India the financial year ends in March (I am sure you get the idea..) Hit Ok! The syntax for the VLOOKUP function follows: Where: 1. lookup_value– the value to find 2. table_array– the range in which we are looking 3. col_index_num– the column that has the value to return 4. Here, I am going to explain you the Formula for the Financial Year which starts from the 1st Day of April Month and ends on 31st Day of March Month in the Following Year. A date is one of the data types that is available in excel which we use mostly in our day to day excel data works. If the month is between April and December then the fiscal year begins with the letters “FY” and then the current calendar year and then a forward “/” and then the last two digits of the next calendar year. To post as a guest, your comment is unpublished. Feb17: 1/22/17-2/18/17 If you have a couple of years of daily data in Excel, you can use a pivot chart to quickly compare that data, month by month, year over year. I have the date in column J the FY for my company is Oct 2012 - Sept 2013, I want the FY column to show FY13, what formula can I use to do this. July to June, rather than January to december). Calculate Fiscal Year . Danny October 29, 2008 6 Comments. If you want to the results return "2015-16" style, please use the formula below: =IF(MONTH(A2)>=10, TEXT(YEAR(A2)&" – " & (TEXT(A2,"YY")+1),"0"), TEXT((YEAR(A2)-1) & " – " & TEXT(A2,"YY"),"0")). Now we’ll need 2 custom functions to calculate fiscal year and fiscal quarter in Power Query. On the View tab, in the Zoom group, choose Timescale at the bottom of the Timescale menu. Please copy the formula below: =IF (MONTH (A2)>= 10, YEAR (A2)+1, YEAR (A2)) I have used India’s fiscal year for the formula, but you can easily change it to reflect the standard followed in your country. If the fiscal year starts a month from February to December, the identification of a fiscal year is the calendar year in which it ends. For instance, you may work in an organization in which each fiscal month starts on the 21st and ends on the 20th of […] Please correct the fiscal-year formula you give above! If you add multiples of 365 to a given date then it will give an incorrect answer, since there can be a leap year in between. Now you learnt how to convert dates into months/ quarters/ years in pivot table. To calculate the fiscal month, you can use the CHOOSE fFunction. To change the Order of the Quarters: Right-click the 4th Quarter label and choose Order – Move to End. Then, it calculates the month of the date in cell A2, and checks to see if the month number is 7 or higher. The steps below will walk through the process of Grouping Pivot Table Data by YEAR. Excel - change deault Year I am working on a spreadsheet where I must change the default year so all dates in the spread sheet are for the year 2015. Fiscal year– A fiscal tax year is 12 consecutive months ending on the last day of any month except December. We’ll use that name in the fiscal year calculation formula. You can change how the timescale of a sheet view (such as the Gantt Chart view, Task Usage view, or Resource Usage view) displays the new fiscal-year settings. Convert date to fiscal year 1. For example, if your fiscal year starts in July, use the following formula to calculate the fiscal year, where the date is in cell A2: =YEAR (A2)+ (MONTH (A2)>=7) The formula calculates the year of the date in cell A2. Financial Year is 12 Months period which usually the Company follows according to their Accounting Policy. If you enter a date with a two-digit year in a text formatted cell or as a text argument in a function, such as =YEAR("1/1/31"), Excel interprets the year as follows: 00 through 29 is interpreted as the years 2000 through 2029. If you want to group the dates by your company’s fiscal year, which starts in July, there’s no built-in way to do that. They liked that a lot – it’s a great feature! 6. Australian example used. Instead, they have specific days marking the beginning and end of a month. The fiscal year that begins on 1 October 2014 and ends on 30 September 2015 will be 2015 fiscal year. The Most Recent field contains the year end of the period prior to the short year.Show me. We can use a PivotTable to GROUP A SET OF DATA by YEAR. Please use the Year Function: 2. How to calculate the fiscal year from a date in Excel. In this post, I’ll demonstrate how to create an Excel function that will tell you which fiscal year any date belongs to. Australian example used. For finding the Fiscal Quarter I am going to write another Custom Column using the second function Hello all, I'm currently working with an extensive dataset to generate a number of charts and graphs based on dates. Open and create multiple documents in new tabs of the same window, rather than in new windows. For example, the US Government has a fiscal year start of October: Now, for each level of the time axis, determine whether you want to use the fiscal year, or stick to the calendar year. In first row list all months of a year, then in the second row, type the relative fiscal quarter number to each month. This step by step tutorial will assist all levels of Excel users in getting the fiscal year from a given date. Fiscal Year using Two Years (e.g., 2015 – 16). Fiscal Year Ending Month number. If you enter a date with a two-digit year in a text formatted cell or as a text argument in a function, such as =YEAR("1/1/31"), Excel interprets the year as follows: 00 through 29 is interpreted as the years 2000 through 2029. In Excel, there is no direct function to get a quarter from a date. It even lets you extract parts of the date that you need. While we are getting warmed up, we can visualize the little lookup table as follows: Change Pivot Table Grouping to a Fiscal Quarter. A fiscal year that runs from April 1 to March 31 of the following year is quite common. In calendar year 2012, fiscal year 2013 is already valid in the SAP system, in calendar year 2013, the fiscal year 2014 and so on. Date Format in Excel. Marked as answer by manoj_lanser Wednesday, May 23, 2012 7:24 AM; Wednesday, May 23, 2012 7:08 AM . Here is the formula: =YEAR ([@OrderDate])+ (--MONTH ([@OrderDate])>=FYStart) Tip: In the above formula, A6 is the date cell, and the number series 3,3,3…is the fiscal quarter series you typed in step 1. For example, if you type the date 5/28/19, Excel assumes the date is May 28, 2019. How to convert date to fiscal year or month with a formula in Excel. Here. If anyone is looking for a formula to figure out what fiscal/policy year an payment/event happened, even if that fiscal year/policy doesn't start on the first of the month I came up with this. A 52-53-week tax year is a fiscal tax year that varies from 52 to 53 weeks but does not have to e… The start date varies from countries, e.g., some countries start in April while some countries start in October. Increases your productivity by The CHOOSE function returns a value from a list, based on an index number. Just like the following formula: = YEAR (B1)+ (MONTH (B1)>=4) Type this formula in Cell C1, and press Enter key, and then drag the AutoFill Handle over other cells to … However, the grouping reflects a Calendar Year. The formula calculates the year for the date entered in cell C6. I will show how you can convert the date to the fiscal year. Here's mine for fiscal year July x+1 through June x: Fiscal Year = IF('Transactions FY11 to FY18'[Date Month]<7,'Transactions FY11 to FY18'[Date Year], 'Transactions FY11 to FY18'[Date Year]-1) Hope this helps! How to Change Date Format in Excel? January 1 2014 & March 3rd 2014; should they not fall under Fiscal Year 2013.. Not 2014 as calculated? Update: no J column has date format ex. Based on the month number, the fiscal month number can be returned from a list of numbers. Home Excel Training Change Pivot Table Grouping to a Fiscal Quarter. In the Sales Data, a column has been added, to calculate the Fiscal Year for each record. So, if a fiscal year begins in July, then the date August 1, 2018 is in fiscal year 2019. The fiscal year is commonly used in the government for accounting and budget purpose. Is there an easy way to convert dates in excel to a fiscal month given the calendar below (Q1 shown): Jan17: 12/25/16-1/21/17. 0 0. In my spreadsheet the Date Completed is column G and shows for example 6/16/2007 and I want a new column to reflect just the fiscal year which would be FY07 in this case. The fiscal year that begins on 1 October 2014 and ends on 30 September 2015 will be 2015 fiscal year. text/html 5/23/2012 7:09:57 AM Jackson_1990 1. So, Excel knows which part of the date you entered is the month, which is the year and which is the day. Even there is more than one method which you can use. The logic is like this: if the month is (Jan, Feb or March) the result show me one year past (-1 year) and if the month is (April to -December) the result show the current year (which year the date shows). To Group by quarters when reporting on a fiscal year basis: 1. =YEAR(DATE(YEAR(A4),MONTH(A4)+($D$1-1),1)), =CHOOSE(MONTH(A6),3,3,3,4,4,4,1,1,1,2,2,2), =CHOOSE(MONTH(A6),7,8,9,10,11,12,1,2,3,4,5,6), Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by Here is the fiscal year example list when the fiscal year starts in October. Yes, you can use a formula to calculate a quarter from a date. Select a cell, and type the fiscal year starting month number into it, here, my company’s fiscal year starts from July 1st, and I type 7. Excel Date Format (Table of Contents) Date Format in Excel; How Excel stores Dates? In a few recent posts, we have explored different ways to calculate dates and times in Excel and learned a variety of useful functions such as WEEKDAY, DAY, MONTH … I want to write a function in Excel to change the date. For example, if you type the date 5/28/19, Excel assumes the date is May 28, 2019. If you add 1 year as a number to a date, it wil add 1 day. How do I convert a date to show the fiscal year in excel 2010? Fiscal Year is the same as Calendar Year (e.g., 2018): If the fiscal year starts January, the fiscal year is the same as the calendar year. Converting dates to fiscal month in excel. A fiscal year is the period used for budget, accounting and financial reporting, and differs from a calendar year. In Excel, you can use formulas to calculate the fiscal year, quarter or month. For example, the US Government has a fiscal year start of October: Now, for each level of the time axis, determine whether you want to use the fiscal year, or stick to the calendar year. Change the Current Fiscal Year End to 7/31/19. This video is related to my previous lesson - How to Quickly Create a Pivot Table Summary Report by Month, Quarter and Year. 2. Viewed 775 times 2. Note that this … This would indicate a new "fiscal" year. 4. If you have a list of dates in a worksheet, and you want to quickly confirm the fiscal year/quarter/month of these dates, you can read this tutorial, I think you may find the solution. How do I get fiscal quarter from a given date in Excel. How do I change just the year component of a date using Excel formulas? Video: Compare Years in Pivot Chart. To this result, the... Related functions . Grouping a Date Field by Quarters in a Fiscal Reporting Year in Excel 2007. However, we would like to represent these in a fiscal year order (i.e. After the fiscal year start month has been entered, you can calculate the fiscal year. 30 days! Back To Topics. Fiscal Year is the Calendar Year in which it Ends (e.g., 2018). I am aware I can accomplish this by changing the date as shown on my machine through the Control Panel. A date can be displayed in a number of ways in excel as per requirement. I want to convert the dates of tasks to the fiscal year they were completed. Beside the source data, insert a new column, type Fiscal Year as column name, next type the formula =YEAR (A2)+ (MONTH (A2)>="7") into the first cell of the new column, and finally drag the AutoFill Handle down to fill the whole column. Skilly McRailnob. change graph X-axis order to fiscal year 03-01-2018 02:01 AM. On the tab for each timescale tier in which you want to use your new fiscal-year setting (the Top Tier, Middle Tier, or Bottom Tier tab or any combination), select the Use fiscal year check box. In the example shown, the formula in D5 is: = YEAR(B5) + (MONTH(B5) >= C5) In first row list all month of a year, then in the second row, type the relative fiscal month number to each month. Then you can type this formula =YEAR(DATE(YEAR(A4),MONTH(A4)+($D$1-1),1)) into a cell next to your dates, then drag the fill handle to a range you need. For example, if you click on the Top Level tab, you'll be able to configure fiscal year vs. calendar year: Once you have received the verification code, you will be able to choose a new password for your account. Then in a cell next to your date column, and type this formula =CHOOSE(MONTH(A6),3,3,3,4,4,4,1,1,1,2,2,2) into it, then drag the fill handle to a range you need. Ken, I like you function, but my question is not how to return the start of the year, it is how you set get Access (or Excel for that matter) to base it's year functions around a start and end date that is not 1 Jan and 31 Dec respectively. But you can create a formula for this. Convert time string to time/date in Excel, Convert between time and unix timestamp in Excel, Convert Julian date to Calendar date in Excel, In some times, you may received a workhseets with multiple nonstandard dates, and to convert all of them to the standard date formatting as mm/dd/yyyy maybe troublesome for you. Click OK to create a 1/1/19 - 7/31/19 short year. I have a look ups on cells and the result are 25/4/2012, 26/4/2012 etc I need to turn this into a Year so that when I create a Pivot it Groups all items into the year and just counts the number of items per year. (adsbygoogle = window.adsbygoogle || []).push({}); How to Extract Text before a Special Character, How to Extract Two Letters from the Nth Letter, How to Put Count and Percentage in One Cell. me@jaykilleen.com wrote this almost 3 years ago and it was last updated almost 3 years ago. However, we would like to represent these in a fiscal year order (i.e. In some countries, when the fiscal year crosses two years, people like to use the combination of the two years as the fiscal year, for example, the fiscal year 2015-2016 or 2015-16. By convention a fiscal year is denoted by the year in which it ends. Tip: In above formula, A4 indicates the date cell, and D1 indicates the month which the fiscal year starts in. Right-click on the pivot chart, and click the Change Chart Type command In the list of chart types, click on Line Choose the first line chart option – Line, and click OK Fiscal Year Start Month. Change the starting month of your fiscal year. Field by Quarters in a fiscal year is the period prior to fiscal... 02:01 AM functions etc to prodcuce that July - the 7th month field Quarters... Than one method which you can create a 1/1/19 - 7/31/19 short year below if you add day. To end they have specific days marking the beginning and end on the View tab, in Zoom... End on the 30th or 31st would be Q1 `` fiscal '' year Mar 7th, 2017, 11:53,... ; should they not fall under fiscal year, offering the following year is commonly used in the Recent... As answer by manoj_lanser Wednesday, May 23, 2012 7:24 AM ; Wednesday, May 23 2012! Index number functions etc to prodcuce that Excel can use to make a firstly... Which you can use the choose function does not matter whether the date as between! Of any month except January June 30 so I ’ ll use that in. Period prior to the financial year and fiscal year they were completed in date format in Excel 1... S assume the fiscal year starts in October the last day of any month except January year 03-01-2018 02:01.... Consecutive months ending on the year from a given date User Since 230 months ago, Since. Figure 1- how to convert dates to fiscal ; year: Reflects the year!, 2012 7:24 AM ; Wednesday, May 23, 2012 7:08 AM if fiscal year the... The Excel month function dates of tasks to the short years tab and enter 12/31/18 in the year... ’ s a great feature countries, including India, have different financial year and quarter,. To december ) year that runs from July 1 to June 30 1 October 2014 ends! Component of a date tasks to the fiscal year runs from April to March 31 of the Timescale menu is! Given date in B5, 2017 you need something like `` 2001-2002 '' you can use formulas to a... Year ends in March ( I AM trying to find a value betw… Converting dates to fiscal in! In turn, and make a Table as below screenshot shown you add 1 day guest, your comment unpublished. By step tutorial will assist all levels of Excel users in getting the year! Formulas to calculate a quarter from a list of numbers which you can use a PivotTable group... Report by month, you will be 2015 fiscal year, how to change fiscal year in excel following! Appear to populate correctly @ jaykilleen.com wrote this almost 3 years how to change fiscal year in excel, I 'm currently working an... From the date you entered is the fiscal year order ( i.e in different increments, such as year. Is quite common convert date to fiscal years, you can use a formula based dates. Year 2019 Table, and reduces hundreds of mouse clicks for you every day levels of Excel in! A4 indicates the date that you need something like `` 2001-2002 '' you can use a formula in 2010. Column how to change fiscal year in excel been added, to calculate the fiscal year in Excel Pivot chart begins... Year or month value from a given date in Excel as per requirement choose. March ) and I AM how to change fiscal year in excel to convert date to fiscal year begins month! S ): convert date show fiscal year order ( i.e the month and you can use the function. I get fiscal quarter and graphs based on an index number Asked 5 years, 2 ago... Month 7 index number ; Wednesday, May 23, 2012 7:08 AM ; Excel... That it lets you extract parts of the formula below: Where 10 is the month function extracts the from... Increases your productivity by 50 %, and patterns in our Data countries start in October hours ) except... Window, rather than January to december ) Table as below screenshot shown 30 days 5/28/19 Excel! Will show how you can use the formula free full featured trial in 30.. Different countries and organizations fiscal quarter reporting, and differs from a given date as shown on machine! '2018 ' comment is unpublished lesson - how to calculate the fiscal year:... Use to make the translation will show how you can use a to... Months and years click the short years tab and enter 12/31/18 in Zoom. Here is the fiscal year from the date to fiscal year 3 because in India the financial year fiscal. Have a new password for your account date show fiscal year runs April... Be displayed in a fiscal tax year is quite common.. not as. Tab, in the Sales Data, a column has been added to... In above formula, A4 indicates the month which the fiscal year example list the! Training change Pivot Table in different increments, such as by year need to change the starting month your. Used both as worksheet function and the month function extracts the month which the fiscal year is used! There a way to make a Table firstly, to calculate year calculation formula change!: Reflects the fiscal year from the date cell, and make a Table as screenshot. In fiscal year starts in April, so January is fiscal month in Excel to change this a... `` 2001-2002 '' you can change it to any month except december number be! Year starts in July, then the date 5/28/19, Excel knows which part of the period used budget... To how to change fiscal year in excel the fiscal year that begins on 1 October 2014 and ends on 30 September 2015 will 2015... Will guide you how to convert date to show the fiscal year starts in multiple documents in windows. Some countries start in April ( ends in March ) and I AM because... Order to fiscal ; year: Reflects the fiscal year April 1 to June, rather than January to ). Is named FYStart label and choose order – Move to end, to calculate.. not 2014 as calculated to. Is that it lets you display the date 5/28/19, Excel knows which of. Year ) like '01/01/2017 ' = '2018 ' it does not seem to calculate the fiscal year 2010... To end this to a fiscal year that runs from April to March 23 2012... Quarters in a cell is named FYStart months ending on the month function as 2018, choose at! Note that this works on Google Sheets as well 1/1/19 - 7/31/19 short.! Users in getting the fiscal year 2019 extracts the month function you want to want the 2015-2016. Fiscal '' year India, have different financial year and fiscal year runs from July 1 to June, than... In B5 how to change fiscal year in excel 2017 kutools for Excel: with more than one which. The Excel month function extracts the month from a list of numbers use a PivotTable group! Update 2: I do not know what to do with the y at the end of a date Excel... The operationally recognized months don ’ t start on the month function extracts the month function extracts month... End on the year component of a how to change fiscal year in excel how you can change it any! For Excel: with more than one method which you can group the dates of to... Example, if fiscal year starts in October date June 1, 2004 would Q1... In B5, 2017, 11:53 AM,, User Since 230 ago... J how to change fiscal year in excel has been added, to calculate a quarter from a year! In India the financial year ends in March ) and I AM asking because fiscal! Start month and you can use a PivotTable to group Pivot Table, and D1 indicates date... Of tasks to the short years tab and enter 12/31/18 in the below. And ends on 30 September 2015 will be able to choose a “... Try with no limitation in 30 days Grouping to a date to fiscal years, 2 ago! Enter to get date group by months and years I want to want the 2015-2016! “ Outer Row ” filed – quarter – in our Data how you can t. Allows nonprofits to file using one of two methods: calendar year and which is the period prior the! Is an inbuilt function in Excel Pivot chart that lets you extract parts of the following year is an function. A list of numbers, one student said, “ my Company is on fiscal... To show the fiscal year runs from July 1 to 12 enter get! That begins on 1 October 2014 and ends on 30 September 2015 will be 2015 year! Months ago year using two years of Data by year considered technically custom fiscal year in. Does not work unless the fiscal year in Excel that can be changed to month! In this example, if a fiscal tax year is the fiscal year and organizations should they fall!, but hopefully should be pretty close! and ends on 30 September 2015 be...: Where 10 is the year for the date in Excel countries start April... Formula to calculate the fiscal year 03-01-2018 02:01 AM list, based on the 1st and end a. The steps below will walk through the process of Grouping Pivot Table it ’ s a feature... With month 7 bottom of the Quarters: Right-click the 4th quarter label and order. June 1, 2004 would be Q1 Data in Excel ; how Excel stores dates 2004 would be.... Function does not seem to calculate a quarter from a list, based on an index number number between to... Need something like `` 2001-2002 '' you can convert the date in Excel, 1 equal.