Replacing all dates of any format to MMM YYYY and calculating times between the dates

Questions and Help
1

Me again!

I'm stumped here hoping y'all can help.
I get documents with a start and end date in multiple formats and need to clean them up to MMM YYYY, then calculate the months between so the output looks like:

MMM YYYY – MMM YYYY ([duration in months] months)

the input is usually MMMM YYYY or MMM YYYY or YYYY and the two dates can be separated with either a space, multiple formats of hyphens, or even the word "to"

I'm not sure even where to begin here

Thanks!

2

Hi @jheilmann,

I tried creating a snippet using our datetime functions and function blocks. Let me know if this works for your use case.

Enter the date range: {formtext: name=date_range; default=Oct 2024 - Nov 2024}
{note}
{dates=splitregex(date_range, "\s*[-–—]+\s*|\s+to\s+")}
{start_date=trim(dates[1])}
{end_date=trim(dates[2])}

{parse_date=(date) -> block
if testregex(date, "[1]{4}$")
return datetimeparse(date, "YYYY")
elseif testregex(date, "[2]{3,} [0-9]{4}$")
return datetimeparse(date, "MMMM YYYY")
else
return datetimeparse(date, "MMM YYYY")
endif
endblock}

{start_parsed=parse_date(start_date)}
{end_parsed=parse_date(end_date)}

{start_formatted=datetimeformat(start_parsed, "MMM YYYY")}
{end_formatted=datetimeformat(end_parsed, "MMM YYYY")}

{months_between=datetimediff(start_parsed, end_parsed, "M")}
{endnote}
{=start_formatted} – {=end_formatted} ([{=months_between}] months)

  1. 0-9 ↩ī¸Ž

  2. A-Za-z ↩ī¸Ž

1 Like
3

yes yes. that's perfect!! I just made a couple of changes so it pulls from the clipboard instead of a form and doesn't add any unwanted new lines.

{date_range={clipboard}}{dates=splitregex(date_range, "\s*[-–—]+\s*|\s+to\s+")}{start_date=trim(dates[1])}{end_date=trim(dates[2])}{parse_date=(date) -> block
if testregex(date, "[1]{4}$")
return datetimeparse(date, "YYYY")
elseif testregex(date, "[2]{3,} [0-9]{4}$")
return datetimeparse(date, "MMMM YYYY")
else
return datetimeparse(date, "MMM YYYY")
endif
endblock}{start_parsed=parse_date(start_date)}{end_parsed=parse_date(end_date)}{start_formatted=datetimeformat(start_parsed, "MMM YYYY")}{end_formatted=datetimeformat(end_parsed, "MMM YYYY")}{months_between=datetimediff(start_parsed, end_parsed, "M")}{=start_formatted} – {=end_formatted} ({=months_between} months)

1 Like