Convert a text string date like 2023 to a range of of time like 2019-2023

[roattw]

I have a table with "Graduation_Yr" as a text field "2022".   It needs to be converted to the typical range of time in school like 2019-2023.  So each would by Grad Year minus 4.

I made a new integer field called "Year_integer"  to pull Graduation Yr in as integer and then subtract by 4.  It verifies but nothing actually populates:

SELECT CAST([@field:RGraduation_Yr] AS integer) - 4

From that I hope to be able to do another calculated field to subtract the 4 years and concatenate.

Probably a much better way but I dont want to change the field type to integer as it might scrub that data.

I can accomplish getting the field copied using JS below, but would prefer calculated value since a JS takes more resources.

<script type="text/javascript">
 
  var src = document.querySelector('[id*=InsertRecordGraduation_Yr]');
  var dest = document.querySelector('[id*=InsertRecordYear_integer]');
 
  src.onchange = function() {
    dest.value = this.value;
  }
  
</script>

 

[Kronos]

Hi @roattw,

I'm guessing you are going to use this for a Submission form page hence the InsertRecord on your JavaScript code. 

Here is the query I used to get the desired output using Calculated value:

CAST((CAST([@field:Year] as INT) - 4) as VARCHAR) + ' - ' + [@field:Year]

This can be used as well in a formula field as well on your table.

 

Here is my setup and the result on a submission page:

I hope this helps!

[roattw]

Kronos, TY. This is great.  And yes, it is on a submission form.  Worked very well!