Jump to content
  • 0

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



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;


Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

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!

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...