Help

Date conversion to quarter and blanking cells

Topic Labels: Formulas
414 2
cancel
Showing results for 
Search instead for 
Did you mean: 
AutumnalDescent
4 - Data Explorer
4 - Data Explorer

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)
2 Replies 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.