How to convert date to fiscal year/quarter/month in Excel?
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.
Convert date to fiscal quarter
Convert date to fiscal year
1. 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. See screenshot:
2. 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.
Tip: In above formula, A4 indicates the date cell, and D1 indicates the month which the fiscal year starts in.
Convert date to fiscal quarter
If you want to convert date to fiscal quarter, you can do as these:
1. Firstly, you need to make a table as below screenshot shown. In first row list all months of a year, then in the second row, type the relative fiscal quarter number to each month. See screenshot:
2. 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.
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.
Convert date to fiscal month
To convert date to fiscal month, you also need to make a table firstly.
1. In first row list all month of a year, then in the second row, type the relative fiscal month number to each month. See screenshot:
2. Then in a cell next to the column, type this formula =CHOOSE(MONTH(A6),7,8,9,10,11,12,1,2,3,4,5,6) into it, and drag the fill handle to your needed range with this formula. 
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.
Quickly convert nonstandard date to standard date formattiing(mm/dd/yyyy)
|
| 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. Here Kutools for Excel's Conver to Date can quickly convert these nonstandard dates to the standard date formatting with one click. Click for free full featured trial in 30 days! |
 |
| Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
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!
