Skip to main content

As I've searched the Community, big shout out to folks who have shared formulas which convert dates to quarters and fiscal years. I'm currently using the formula below, and I'm trying to find a way to blank out cells which have no date. Any suggestions/help much appreciated!


IF(AND(MONTH({Invoice Date}) >= 1, MONTH({Invoice Date}) <= 3),"Q1",
IF(AND(MONTH({Invoice Date}) >= 4, MONTH({Invoice Date}) <= 6),"Q2",
IF(AND(MONTH({Invoice Date}) >= 7, MONTH({Invoice Date}) <= 9),"Q3",
IF(AND(MONTH({Invoice Date}) >= 10, MONTH({Invoice Date}) <= 12),"Q4"))))
& " FY '" & RIGHT(YEAR({Invoice Date}) & "", 2)

There are a lot of possible formulas. Here is one option.

IF(
{Invoice Date},
CONCATENATE(
"Q" & DATETIME_FORMAT({Invoice Date}, "Q"),
" FY" & DATETIME_FORMAT({Invoice Date}, "YY")
)
)

@AutumnalDescent wrote:

IF(AND(MONTH({Invoice Date}) >= 1, MONTH({Invoice Date}) <= 3),"Q1",
IF(AND(MONTH({Invoice Date}) >= 4, MONTH({Invoice Date}) <= 6),"Q2",
IF(AND(MONTH({Invoice Date}) >= 7, MONTH({Invoice Date}) <= 9),"Q3",
IF(AND(MONTH({Invoice Date}) >= 10, MONTH({Invoice Date}) <= 12),"Q4"))))
can be also replaced by  IF({Invoice Date}, "Q" & (1+INT(MONTH({Invoice Date})/4)) )
Using native quarter formatting, from answer by @kuovonne , is the best choice.

Reply


Terms & Conditions