Skip to main content

Date conversion to quarter and blanking cells

  • September 30, 2023
  • 2 replies
  • 39 views
AutumnalDescent
Forum|alt.badge.img+1

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

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • September 30, 2023

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") ) )
Alexey_Gusev
Forum|alt.badge.img+25

@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.
Terms & ConditionsAccessibility statement