Log in
x
or
x
x
Register
x

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.
doc convert date

arrow blue right bubble Convert date to fiscal year


Kutools for Excel, 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:
doc-convert-fiscal-year-1

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.doc-convert-fiscal-year-2

Tip: In above formula, A4 indicates the date cell, and D1 indicates the month which the fiscal year starts in.


arrow blue right bubble Convert date to fiscal quarter

If you want to convert date to fiscal quarter, you can do as these:

Kutools for Excel, 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:
doc-convert-fiscal-year-3

formula 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.doc-convert-fiscal-year-4

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.


arrow blue right bubble 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:doc-convert-fiscal-year-5

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.

doc-convert-fiscal-year-6
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:

Apply Date formatting (change date to multiple date formatting as you need)

doc apply date formatting


Recommended Productivity Tools

Office Tab

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

Kutools for Excel

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

gold star1 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...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    MikeyMoe316 · 3 months 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.
    Edward · 7 months 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.
      J. Randle · 6 months 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.
    jjcasta3 · 7 months 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!
  • To post as a guest, your comment is unpublished.
    Kamran · 8 months ago
    An easy formula for Fiscal Month:
    =IF(+TEXT(Index!E2,"M")-6
  • To post as a guest, your comment is unpublished.
    charles · 11 months ago
    Please ignore my previous Comment.