Dynamically creating a semester code based on the date field

[emmanuel_varesio]

Hello,

For 6-months periods (from April to September and from October to March the following year) I'm trying to dynamically create a date range code based on the actual date value.

I've uploaded an example where I would like to create the following codes :

- xx/10 - yy/03

- yy/04 - yy/09

- yy/10 - zz/03

where xx, yy & zz is the year in 2-digit format based on the month parsed from the date field (xx = yy-1 and zz = yy+1).

Thanks in advance for your help.

Emmanuel

[danilang · Accepted solution]

Hi @emmanuel_varesio 

You can accomplish this in one formula tool

if datetimemonth([Date])>=4 and datetimemonth([Date])<=9 then
	DateTimeFormat([Date],"%y")+"/04 - "+DateTimeFormat([Date],"%y") +"/09"
elseif DateTimeMonth([Date]) >=10 then
	DateTimeFormat([Date],"%y")+"/10 - "+tostring(tonumber(DateTimeFormat([Date],"%y"))+1) +"/03"
else 
	tostring(tonumber(DateTimeFormat([Date],"%y"))-1)+"/10 - "+DateTimeFormat([Date],"%y") +"/03"
endif

as in the attached workflow.  It's split into the 3 cases,

1) between April and Sep, same year

2) Oct or later, yy and yy+1

3) Mar or before. yy-1 and yy

Dan

[emmanuel_varesio]

Hi Dan,

This is indeed much simpler than what I had in mind ... I was wrongly thinking at binning months 😞

Thank you.

E.