Renaming multiple dates formats into a single one using an expression

[Jaeger1349]

I have a lot of file tags that i want to unify under the same criteria. For the date I have 3 categories:

1. Month Day(th), Year, e.g. January 1st, 2005
2. Incomplete dates, e.g., January 2005
3. Year only, e.g., 2005

I want to make a singular expression to transform all this date to a single format which is yyyy-mm-dd, adding a zero for single digit days (date type 1) ), "01" for date type 2) and "31-12" for date type 3).

I was writing this monstrosity of expression for a format value:

$regexp($regexp($regexp($regexp($regexp($regexp(%YEAR%,^([A-Za-z]+) (\d{1,2})(st|nd|rd|th)?, (\d{4})$, $4-$if($len($2)=1,0$2)-$3),^(\d{4})$, $1-12-31),^(\d{4}) (\d{1,2})$, $1-$if($len($2)=1,0$2)-01),January,01),February,02),March,03),April,04),May,05),June,06),July,07),August,08),September,09),October,10),November,11),December,12)

But for some reason, i can't make it work, the dates remains the same when apply it. Any idea what am i doing wrong?

Thanks in advance

[ohrenkino]

@Jaeger1349

I want to make a singular expression ... Any idea what am i doing wrong?

I wonder where the requirement comes from to do it in a single expression.
You could create an action group with several actions in which each action copes with one part of the transformation, e.g. replacing the order numbers first, then the month names and so on.
The function Convert>Tag-Tag has a preview for scripting functions.
That should help you to debug the expression.
In respect to syntax I find 17 opening brackets and 26 closing ones ... AFAIK these should be in pairs.

[Jaeger1349]

I want it to do it in a single expression just for simplicity (because this reminds me nesting functions in MS excel). Well, i'll check both of your indications. Thanks

[Jaeger1349]

Anyway, if you can help/suggest something to how to approach this, deeply appreciated.

[ohrenkino]

Here is a basic approach to play with:
$regexp('January 1st, 2005',.*(\d\d\d\d),$1) - $replace($regexp('January 1st, 2005',(.+?) .*,$1),January,01) - $num($replace($regexp('January 4th, 2005','.*? (.*?), \d+',$1),1st,01,2nd,02,3rd,03,th,),2)
Replace the fixed date with your field variable, complete the list of months and see how you copy with incomplete dates.

[Jaeger1349]

Finally I gave up with the regexp. I ended up leaning towards to using an action group. Thanks for your help, maybe in a future I'll try again hahaha. I'll left the entire actions if anyone is interested in using it.

[#0]
T=4
F=year
1=([\(\[\{])\s+|\s+([\]\}\)\?\:\;\,\!\.])
2=$1$2
3=0

[#1]
T=4
F=year
1=^\s+|\s+$
2=
3=0

[#2]
T=2
F=year
1=January
2=01
3=0|0

[#3]
T=2
F=year
1=February
2=02
3=0|0

[#4]
T=2
F=year
1=March
2=03
3=0|0

[#5]
T=2
F=year
1=April
2=04
3=0|0

[#6]
T=2
F=year
1=May
2=05
3=0|0

[#7]
T=2
F=year
1=June
2=06
3=0|0

[#8]
T=2
F=year
1=July
2=07
3=0|0

[#9]
T=2
F=year
1=August
2=08
3=0|0

[#10]
T=2
F=year
1=September
2=09
3=0|0

[#11]
T=2
F=year
1=October
2=10
3=0|0

[#12]
T=2
F=year
1=November
2=11
3=0|0

[#13]
T=2
F=year
1=December
2=12
3=0|0

[#14]
T=4
F=year
1=(\d{1,2})(st|nd|rd|th)
2=$1
3=0

[#15]
T=2
F=year
1=(\d{1,2})
2=$if($len($1)
3=0|0

[#16]
T=4
F=year
1=[\s\.,;!?\-()\[\]{}:\'"]
2=
3=0

[#17]
T=5
1=$if($eql($len(%year%),4),1231%year%,%year%)
F=year

[#18]
T=5
1=$if($eql($len(%year%),6),$left(%year%,2)01$mid(%year%,3,$len(%year%)),%year%)
F=year

[#19]
T=5
1=$if($eql($len(%year%),7),$left(%year%,2)0$mid(%year%,3,$len(%year%)),%year%)
F=year

[#20]
T=5
1=$if($eql($len(%year%),8),$mid(%year%,5,$len(%year%))$left(%year%,4),%year%)
F=year

[#21]
T=5
1=$if($eql($len(%year%),8),$left(%year%,4)-$mid(%year%,5,2)-$mid(%year%,7,2),%year%)
F=year

Edit: This is the breakdown of the actions:

[#0] - Remove unwanted characters in the year (spaces and punctuation characters)
[#1] - Strip leading/trailing spaces (just in case)
[#2] to [#13] - Replace month names with numbers
[#14] - Remove ordinal suffixes (st, nd, rd, th)
[#15] - Check if the year is numeric
[#16] - Remove special characters " [\s.,;!?-(){}:'"] "
[#17] to [#21] - Conditional year formatting based on length

• [#17]: If the year length is 4, it adds "1231" before the year.
• [#18]: If the year length is 6, it seems to add "01" in the middle.
• [#19]: Similar to [#18], but for a length of 7.
• [#20]: If the length is 8, it swaps the format to something that seems like a date format.
• [#21]: If the value has 8 digits, it reformats to YYYY-MM-DD.

The last section (actions from 17 to 20) it can be changed according to the user needs (if they need for a single year put "0101" or else).

That's it, this hurt me for a few hours, but i'm happy with the result.

Best regards.

[ohrenkino]

Two remarks:
I doubt that such a representation of actions is really readable for the untrained eye.
Second: by definition the field YEAR is a 4-digit number. If you want to get a more detailed time reference the field RELEASETIME would be the field of choice.

[Jaeger1349]

@ohrenkino

Two remarks:
I doubt that such a representation of actions is really readable for the untrained eye.

Well, sorry but i didn't find a way to show it in a more condensed way. That's a reason I want it as a regexp, for "economy". Anyway, I added an explanation of every action and what it does.

@ohrenkino

Second: by definition the field YEAR is a 4-digit number. If you want to get a more detailed time reference the field RELEASETIME would be the field of choice.

I put "year" as an example field. I have a reserved field in my DB for that value. But the idea is the same.

Best regards.

[ohrenkino]

It would be nice if you could include your action group as a downloadable file.

I am still a little puzzled that you first reject my suggestion

@ohrenkino

You could create an action group with several actions in which each action copes with one part of the transformation, e.g. replacing the order numbers first, then the month names and so on.

as you say

@Jaeger1349

I want it to do it in a single expression just for simplicity

then ignore my one-line suggestion

@ohrenkino

$regexp('January 1st, 2005',.(\d\d\d\d),$1) - $replace($regexp('January 1st, 2005',(.+?) .,$1),January,01) - $num($replace($regexp('January 4th, 2005','.? (.?), \d+',$1),1st,01,2nd,02,3rd,03,th,),2)

only to present an action group with 21 actions - which looks to me exactly like my first suggestion.
In a way I feel like I wasted my time to think of a single-line-scripting-suggestion.

[Jaeger1349]

@ohrenkino

It would be nice if you could include your action group as a downloadable file.

No problem.

@ohrenkino

only to present an action group with 21 actions - which looks to me exactly like my first suggestion.
In a way I feel like I wasted my time to think of a single-line-scripting-suggestion.

In that moment, I felt so frustrated trying to work with regexp a nesting them and overestimating my own capacities, that I began to work with group actions ('cause at least, I understand them a little bit more) and visually it helps me to get it better. Then you replied with an different approach (that I appreciate) but when i began again to working on it with regexp, testing it, debugging it, failing, etc., i was so overwhelmed ans stressed when I didn't make it to work so I left it aside for a moment to clear my mind (that is one of the reasons that I don't really like testing/debugging so much). But personal traumas aside, don't take it personal, your code was really helpful to solve the initial problem.

Regards.

Date Conversion.mta (1.3 KB)

[LyricsLover]

@Jaeger1349

[#2] to [#13] - Replace month names with numbers

For your 12 month-Actions you could do it in 1, adjusting @ohrenkino's $replace suggestion or this $replace idea to your YEAR

Multi condition IF

I would solve it like this: 1.) Use Convert Tag -> Tag with [image] This will replace the right two numbers from the %DATE% to the written month name and then adds the left two numbers from the %DATE% at the end ot the month name, separated with a space. Please complete all 12 possible month numbers and names in the format string. This only works if your days and the month are always saved in the format DDMM in %DATE%. As always: Please backup your tracks before you test and check this c…

The idea is to use multiple parameter pairs in `$replace'.