Tip: Other languages are Google-Translated. You can visit the English version of this link.
or

Register

or
0
0
0
s2smodern

## 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 year

Convert date to fiscal quarter

Convert date to fiscal month

#### Convert to Date

In some times, you can have multiple nonstandard dates in your worksheet, and to convert all of them to the standard date mm/dd/yyyy maybe troublesome for you. Here Kutools for Excel's Conver to Date can quickly convert dates to standard date as mm/dd/yyyy format.

#### Convert date to fiscal year

###### Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

 with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

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:

 with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

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:

 Never need to worry about long long formulas in Excel anymore! Kutools for Excel's Auto Text can add all formulas to a group as auto text, and liberate your brain! Click here to know Auto Text     Click here to get free trial

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.

You may be intested in this:

### Recommended Productivity Tools

#### Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

#### Kutools for Excel

Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

200 New Features for Excel, Make Excel Much Easy and Powerful:

• Merge Cell/Rows/Columns without Losing Data.
• Combine and Consolidate Multiple Sheets and Workbooks.
• Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
• Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
• More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Say something here...
symbols left.
###### or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
• To post as a guest, your comment is unpublished.
· 1 years ago
An easy formula for Fiscal Month:
=IF(+TEXT(Index!E2,"M")-6
• To post as a guest, your comment is unpublished.
· 1 years ago
Please ignore my previous Comment.
• To post as a guest, your comment is unpublished.
· 1 years ago
I believe your formula may be incorrect. If your fiscal year starts in July - the 7th month. This would indicate a new "fiscal" year. January 1 2014 & March 3rd 2014; should they not fall under Fiscal Year 2013.. Not 2014 as calculated? I am asking because my fiscal year starts in April (ends in March) and I am trying to figure out the formula. When I type in 4, it does not appear to populate correctly.
• To post as a guest, your comment is unpublished.
· 2 years ago
I am trying to convert date to fiscal month however the CHOOSE function does not seem to calculate ? IE 30/09/2015 shows 3..

=CHOOSE(MONTH(G4),7,8,9,10,11,12,1,2,3,4,5,6)