Transposing Date Format using Regular Expression

[monicapope]

I have a library with a couple of Date Columns, 1 is a library Date Column the other is a Date Lookup Column (from a list in the same site) that accepts null value. 

Issues:

-  Output from this 2 different Columns Date type in the same format

• Output Column 1 (desirable output):  11/20/2015
• Output Lookup Column
  • Before Regex:  180;#2011-11-30 00:00:00
  • After Regex:  2011-11-30

- Regex syntax:

I used 2 Regular Expression steps to remove the lookup id/token (.*;#) and then to remove the seconds (\s\d\d:\d\d:\d\d).  I then tried to convert value - text to date
(format mm/dd/yyyy) - but I keep getting data type error.

Questions:

- What is a more efficient method or syntax to get the date and convert it to mm/dd/yyyy?
Grouping and transposing using just one Regular Expression step?  (\d{1,}\;\#)([0-9]{4})-([0-9]{2})-([0-9]{2})
([0-9]{2}:[0-9]{2}:[0-9]{2}),  followed by something that removes and transpose.

Like the below for date only? That would apply to  2011-11-30

• Grouping:  ([0-9]{4})-([0-9]{2})-([0-9]{2})
• Transposing:  \2/\3/\1

-  Is there a better way to accomplish this?

- Is there a way to convert text to date? And then format the output as desired?  mm/dd/yy

[monicapope · Accepted solution]

I was able to get it to work this way:

- Store output in txt variable

- Remove lookup id/token in front of the date/time

- Converted txt to date **  without format ** specifying only the culture

- Build string date to txt using FormatDate function (date variable)

[v_reschke]

I found a work around, since I need to learn more about writing Regular Expressions.

I was trying to populate a column called Start Date with the Start Date of a Lookup field (IT Start Date/Time). It would error because it would return the ID of the Lookup Item followed by a ;#, then the date. My workflow was further compounded by the date of the lookup being both date and time and the one I was trying to populate being only a date field. 

First I made sure my lookup column showed the ID field of the Lookup.

Workflow steps

I started with setting a Single line text variable to replace the ID & ;# with nothing.

The IT ID is the name I renamed the Lookup column for the ID. It is usually the Name of the lookup followed by :ID 

My Lookup is called Internal Training, so the ID would normally be Internal Training:ID

Insert Reference

fn-Replace(IT Start Date/Time, IT ID & ";#","")

Then I used a Convert Value action. I used the Mid function to capture just the first 10 characters, since dd/MM/yyyy equals 10 characters.

Insert Reference

fn-Mid(Start SLT,0,10)

Then I just populated the Date field with this variable.

It took out the extraneous characters and it successfully updated the Start Date column I needed it to. 

You can do the same thing for any text columns by using the Replace function.

Insert Reference

fn-Replace(IT Location, IT ID & ";#","")

[monicapope]

Nice.