Calculating Year Field from Date for Fiscal Year

[Tiff]

I'm working on a survey where we want to calculate both calendar year and fiscal year. Calendar year is easily done, but say our fiscal year is from Oct 2023 to Oct 2024 and does not follow the calendar year. Is there an easy way to calculate this directly in the survey upon submission? Thank you!

[SteveCole]

You could use Arcade to look at the current month of the current year. If it's October or later, you would use the current year's value. If not, you would use the previous year's value:

var theDate = Date();
var theMonth = Month(theDate);
  
If(theMonth >= 9) {
 return Year(theDate);
 } else {
 return  Year(theDate)-1;
}

Note: the Month function returns a value from 0-11 so you look for a value of 9 or greater

[Tiff]

Thank you @SteveCole! Is there a way to do this dynamically in Survey123 as well upon the survey submission? If not, Arcade would work but to my knowledge that would require someone to edit the feature for that to populate, right? In the case that we'd want that hard coded into a field (rather than just in a pop-up).

[Raul]

I converted the logic you posted in arcade to javascipt, so OP can do the calculation on the S123 form using javascript fuctions.

function calculateFiscalYear(input_date, startMonth) {
	// Specify the month when the fical year starts
	// Use 1 for January all the way up to 12 for December
	const date = new Date(input_date);
	const curr_month = date.getMonth() +1;
	
	if(curr_month >= startMonth) {
		return date.getFullYear();
	} else {
		return date.getFullYear() + 1;
	}
}

An on the calculation field use:
pulldata("@javascript", "functions.js", "calculateFiscalYear", ${<input_date>}, ${<startMonth>})

[Tiff]

This is really neat, thank you @Raul! I don't think I have combined using javascript functions with Survey123 calculations. Do I need to put the the script somewhere in my Survey123 Connect to be able to reference it?

[Raul]

Yes, you need to use the scripts tab in survey123 connect (bottom right) to include javascript files in your survey.

You can get more details about how to use custom javascript functions in survey123 here:
https://doc.arcgis.com/en/survey123/desktop/create-surveys/pulldatajavascript.htm

[JenniferAcunto]

You might want to check out Extending Survey123 smart forms with custom JS functions. Note that you cannot use JS with public surveys. 

[Tiff]

Ahhh thank you both @Raul @JenniferAcunto . Unfortunately the survey is shared publicly as of right now, so Field Maps may be the best option for this purpose. 

[JenniferAcunto · Accepted solution]

You don't need JS to be able to calculate that. You can use an if statement in the calculation column to get there. 

if(int(format-date(${date1},'%n')) >= 10, int(format-date(${date1},'%Y')), (int(format-date(${date1},'%Y'))-1))

[Tiff]

This worked really well! Thanks, and appreciate everyone else's input!