Select to view content in your preferred language

# Calculating Year Field from Date for Fiscal Year

1413
9
10-17-2023 10:30 AM
by
Occasional Contributor III

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!

Tags (4)
1 Solution

Accepted Solutions
Esri Regular Contributor

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))``

- Jen
9 Replies
Frequent Contributor

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

by
Occasional Contributor III

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).

by
New Contributor III

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>})

by
Occasional Contributor III

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?

by
New Contributor III

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

Esri Regular Contributor

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

- Jen
by
Occasional Contributor III

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.

Esri Regular Contributor

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))``

- Jen
by
Occasional Contributor III

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