AnsweredAssumed Answered

Calculating Yearly Quarters

Question asked by heinrich1223 on Jan 29, 2018
Latest reply on Jan 29, 2018 by heinrich1223

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.

Outcomes