How to automatically update a field by detecting a date range from another custom field in Issues

Question

Hi there, I am trying to apply automation to a custom field in Issues (Let say the name of this field is "Sprint #"). This "Sprint #" automation relies on another custom field, which is a date field (Let say the name of this field is "Date"). Below is the result I aim to have,

1. "Date" in an Issue is updated with a date - Dec 15, 2023

2. By reading the "Date" field, "Sprint #" automatically update a Sprint number by detecting which date range the "Date" falls into. In this case, the "Sprint #" automatically updated as "S50"

3. "Date" in another issue is updated with a date - Jan 15, 2024

4. Again, by detecting this date field, "Sprint #" automatically updated as "S51"

May I ask is that a way that I can accomplish this please? It is similar to vlookup in Excel which looking up a date range and returns a value. Thanks!

Bill Sheboy · Answer

Hi @Victor -- Welcome to the Atlassian Community!

Is your "Sprint #" a calculated field based on the "Date" or is it a lookup, as you described when doing this in a spreadsheet with VLookup?

If it is a calculation, you could try math and text functions to do this.

If it is a lookup, please try using the Lookup Tables action for rules.

Kind regards,
Bill

Victor_t5jxu4 · Answer

Thanks Sebastian! I think I do have the Scriptrunner app.

Would you mind to share what would the script be like if I want to achieve this please? Thanks!

Sebastian Krzewiński · Answer

Hi @Victor

I'm not sure that this can be achieved by automation rules. Only what came to my mind is to use script runner scripts but I don't know if you have this app.

Regards,
Seba