Instead, the =CHOOSE() function would let you specify 12 quarter numbers that correspond to months 1 through 12. The array constant inside the curly braces holds a 5-row by 2-column lookup table. But the formula =VLOOKUP(MONTH(A2),2) is difficult to remember and type. You could use a VLOOKUP formula to convert month number to quarter number. This result is limited to 12 integers from 1 to 12. To calculate a fiscal quarter, first convert the date to a month number using =MONTH(). Government contractors might use a fiscal year end of September 30. In Figure 2, cell A7 uses a formula of =“Q”&ROUNDUP(MONTH(A2)/3,0)&“-”&YEAR(A2) to return “Q1-2017.”įiscal years ending January 31 are common in retail companies. To show the quarter as “Q1” or “Qtr 1,” concatenate the appropriate text before the result. By using the =ROUNDUP() function, you will have 1 for January, February, and March 2 for April through June and so on.
Dates in January will return 0.33, February 0.67, March 1.00, April 1.33, and so on. (Figure 1 shows the Excel functions that you can use to convert a date into years, months, weeks, or weekdays.) First, use the =MONTH() function to convert the date to a month number.
If your financial year ends on December 31, then you can use a short formula.
0 kommentar(er)
