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,

Say something here...
symbols left.
###### or post as a guest, but your post won't be published automatically.
• To post as a guest, your comment is unpublished.
· 3 months ago
You can use below formulae:

Here Col K2 is our date column which is to be converted in format 20XX-XY (e.g. 2017-18).

=IF(MONTH(K2)<4,TEXT(DATE(YEAR(K2)-1,MONTH(K2),DAY(K2)),"YYYY-")&TEXT(DATE(YEAR(K2),MONTH(K2),DAY(K2)),"YY"),TEXT(DATE(YEAR(K2),MONTH(K2),DAY(K2)),"YYYY-")&TEXT(DATE(YEAR(K2)+1,MONTH(K2),DAY(K2)),"YY"))
• To post as a guest, your comment is unpublished.
· 1 months ago
thank you
• To post as a guest, your comment is unpublished.
· 8 months ago
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):
FISCAL YEAR =YEAR(A4)+(MONTH(A4)]=\$D\$1)
FISCAL MONTH =MOD(MONTH(A4)-\$D\$1;12)+1
FISCAL QUARTER =QUOTIENT(MOD(MONTH(A4)-\$D\$1;12);3)+1

How it works (examples assume FY starting month in \$D\$1 = July = 7):

As in MS EXCEL the equivalent value for TRUE = 1 and FALSE = 0, the FISCAL YEAR can also be calculated without the IF clause as:
FISCAL YEAR =YEAR(A4)+(MONTH(A4)]=\$D\$1)

B. In the Fiscal Month formula, the innermost term [MONTH(A4) - \$D\$1] will shift the months by the FY starting month, i.e. JUL(7) - StartMonth(7) = 7 - 7 = 0.
So the result for date in fiscal start month = 0, while all others range from -11..+11, e.g. Jul = 0, Aug= 1, ... , Dec = 5; Jan = -6; Feb = -5; Jun = -1

C. Running the result [B] above through the term [MOD([B];12)], will retain the positive numbers, while increasing any negative [B] by 12.
Now the results range from 0.11, e.g. Jul = 0, Aug= 1, ... , Dec = 5; Jan = 6; Feb = 7; Jun = 11

D. The final [+1] term will adjust the 0..11 range of [C] to be the correct 1..12 fiscal month range, so the FiscalStartMonth = 1, et voila:
e.g. Jul = 1, Aug= 2, ... , Dec = 6; Jan = 7; Feb = 8; Jun = 12

E. Converting a FISCAL MONTH to a FISCAL QUARTER becomes relatively easy then, knowing that quarter (= 3 months) can be calculated by
QUARTER = QUOTIENT( [Month] - 1 ; 3) + 1

F. Replacing [Month] with [Fiscal Month] in above formula to get .......... QUOTIENT( [Fiscal Month] - 1 ; 3) + 1
Now we can insert the FISCAL MONTH formula from above and get .... QUOTIENT( MOD(MONTH(A4)-\$D\$1;12)+1 - 1 ; 3) + 1
The [+1 -1] in the middle can be removed, and we end up with ............. QUOTIENT( MOD(MONTH(A4)-\$D\$1;12) ; 3) + 1
• To post as a guest, your comment is unpublished.
· 1 years ago
In the Fiscal Year example, the formula in cell D4 should be:
=YEAR(A4)+IF(MONTH(A4)>=\$D\$1,1,0)
This will work for any fiscal year, not just one that starts with July
• To post as a guest, your comment is unpublished.
· 1 years ago
Hi

Our fiscal year starts in February and ends in January of the following year (for example 1-Feb-2017 to 31-Jan-2018 should = Fiscal Year 2018, hence 31-Jul-2017 should be in Fiscal Year 2018, your formula computes Fiscal Year 2017). So I did everything as you stated above and changed the "Fiscal year starting month" to 2 and it does not calculate it right for some dates. I am not sure why. I would appreciate if you could please get back to me. Many thanks
• To post as a guest, your comment is unpublished.
· 1 years ago
[quote name="Edward"]Hi

Our fiscal year starts in February and ends in January of the following year (for example 1-Feb-2017 to 31-Jan-2018 should = Fiscal Year 2018, hence 31-Jul-2017 should be in Fiscal Year 2018, your formula computes Fiscal Year 2017). So I did everything as you stated above and changed the "Fiscal year starting month" to 2 and it does not calculate it right for some dates. I am not sure why. I would appreciate if you could please get back to me. Many thanks[/quote]

Would this work for you?

=IF(MONTH(A1)>=2,YEAR(A1)+1,YEAR(A1))
Any month >=Feb would return the current year+1 which is your FY. Otherwise, it will only return the year as is. When I did Ctrl+: in cell A1 to paste date in MS Excel, it returned 2018. Changed the 04 to a 01 and it returned 2017.
• To post as a guest, your comment is unpublished.
· 10 months ago
WOW so easy thanks!!
• To post as a guest, your comment is unpublished.
· 1 years ago
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?

Thanks for the post!