Calculating Yearly Quarters

[PaulPanehal]

Hello all,

I wanted to see if anyone had an idea for auto-filling a text field with the correct year quarter based on a series of IF-THEN statements. I am having an error.

This is what I presently have:

IF(AND(${INSPECTION_DATE}>=DATE(YEAR(${INSPECTION_DATE}),1,1),${INSPECTION_DATE}<=DATE(YEAR(${INSPECTION_DATE}),3,31)),"Q1",IF(AND(${INSPECTION_DATE}>=DATE(YEAR(${INSPECTION_DATE}),4,1),${INSPECTION_DATE}<=DATE(YEAR(${INSPECTION_DATE}),6,30)),"Q2",IF(AND(${INSPECTION_DATE}>=DATE(YEAR(${INSPECTION_DATE}),7,1),${INSPECTION_DATE}<=DATE(yEAR(${INSPECTION_DATE},9,30)),"Q3",IF(AND(${INSPECTION_DATE}>=DATE(YEAR(${INSPECTION_DATE}),10,1),${INSPECTION_DATE}<=DATE(YEAR(${INSPECTION_DATE}),12,31)),"Q4")))))
‍

Basically, there are a series of nested IF statements where I am trying to define the range for each quarter of the year. I am using a field that uses a TODAY() [${INSPECTION_DATE} is TODAY()] function to define the correct year for this calculation. Of course this statement works well within Excel itself, but not so much for Survey123 where it cannot parse through my statement.

I think it has everything to do with not having the AND(), DATE(), YEAR() functions added into its framework, or having different functionality. I may also be overwhelming the parser.

My ultimate question is if there is a way to calculate a range of dates that are based on set quarters (month and day), and auto-fill a text field with the correct quarter (Q1, Q2, Q3, Q4) based on the result. Like I said, the above function works in Excel, but not for the XLSForm syntax.

This is all there really is to it. This field is intended to be read-only and auto-calculated for field inspections.

[JamesTedrick · Accepted solution]

Hi Paul,

Though Survey123 Connect does use Microsoft Excel to author surveys, it does not use Microsoft Excel functions.  You'll find information on the functions it does support at Formulas—Survey123 for ArcGIS | ArcGIS .

ON determining the quarter of a year, there are a couple of ways to accomplish this.  The most important function in this case is the format-date() function, which you can extract the month value, which then can be used in either calculations or regular expression evaluations.  Below is a sample calculation to generate "Q1", "Q2", "Q3", "Q4":

concat("Q", int(format-date(${d1}, '%n') div 4 + 1))

- format-date generates the month as a number

- the month divided by 4 + 1 gets us a value from 1.333 to 4.000 that aligns to the quarters

- the int function drops off the fractional component

- the concat function joins the letter Q with the month value.

[PaulPanehal]

Thanks for getting back to me James,

I have looked into your formula, and it was close to what I needed. I looked through other solutions for excel, and came up with this formula.

concat("Q",int((int(format-date(${INSPECTION_DATE}, '%n'))+2) div 3))‍

This ensures my quarters are:

• Q1: Jan 1 - Mar 30
• Q2: Apr. 1 - Jun 30
• Q3: Jul 1 - Sept 30
• Q4 Oct 1 - Dec 31

I appreciate you giving me the right footing. This will not somehow be affected by epoch timing, or some other standard, correct?