Calculate Quarter by Date

Hi all,

I want to auto calculate the fiscal year quarter based on the date a form was filled out. I want to do this in a hidden value. I am having trouble finding examples of if statements that classify a date range as Quart 1, if between 2 dates.

Does the below look correct? FY18, Quarter 4 is coded numerically at 1804 (YY0quarter), whereas FY19, Quarter 1 is 1901.

if(#form/date_submission >= (2018 - 7 - 1) and #form/date_submission < (2018 - 9 - 30), “1804”, " ") and if(#form/date_submission >= (2018 - 10 - 31) and #form/date_submission < (2018 - 12 - 31), “1901”, " ") and…

Does that seem correct?

Amanda

You can try this

concat(format-date(#form/PrsntDate, “%y”),

cond(format-date(#form/PrsntDate, “%m”)< 4, “01”, format-date(#form/PrsntDate, “%m”)< 7, “02”, format-date(#form/PrsntDate, “%m”)< 10, “03”, “04”))

This formula has two parts

  1. format-date(#form/PrsntDate, “%y”), - generates the last part of the year
  2. cond(format-date(#form/PrsntDate, “%m”)< 4, “01”, format-date(#form/PrsntDate, “%m”)< 7, “02”, format-date(#form/PrsntDate, “%m”)< 10, “03”, “04”) - would generate Quarter for any date. This is done by extracting the month part.
  3. concat(…, …) combines the values returend by points 1 and 2

Hope this would work

Thanks! Will try now.

Please note that in the formulae: format-date(#form/PrsntDate, “%y”), “y” is i small case. The capital returns all the four digits