=YEAR(A4)+((MONTH(A4)>=MONTH($D$1)+(DAY(A4)<Day($D$1)))-1)
D1 in this has to be a full date 01/06/2019 for example.
Also surely a cheeky Vlookup is a better way of doing the quarter formula
=Vlookup(Month(A6),etc.)
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
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.
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.
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. |