Quarter Formula for July Fiscal Year - June not Working!

Platform discussions Special workflows & use cases
,
1

Hi! I’ve been researching several forums regarding formulas that display an off-cycle quarter (i.e.: our fiscal year starts in July, ends in June). I found a few formulas that almost gives me what we need:

“Q” & FORMAT_DATE(EDATE(FORMAT_DATE({Due Date}), 7), “Q”)

IF({Due Date},“Q”&FORMAT_DATE(EDATE({Due Date},7),“Q - YY”),“Q”)

BUT, in either case, I keep running into the issue of June stating Q1 instead of Q4 and I am using a due date column to reference the timing, would a timeline be easier in this case? Also is there a workaround for the qNaN showing up for the empty due date fields?

Could someone assist with the correct formula to use? Please help, my brain is fried at this point!

2

Hi Erika,

EDATE(FORMAT_DATE({Due Date}),7) adds 7 months to your date, so for sure, if you’re in June, it will give you a date in January, not December. Change it to 6.

Try:

IF({Due Date},"Q" & FORMAT_DATE(EDATE(FORMAT_DATE({Due Date}),6),"Q"),"")

 
 
Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

  • Create formulas without using the Formula column (and avoid its limitations)
  • Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.

1 Like
3

Ahh! Success! Thank you so much - it worked like a charm!