AnsweredAssumed Answered

Transposing Date Format using Regular Expression

Question asked by monicapope on Nov 19, 2015
Latest reply on Jan 4, 2018 by 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

Outcomes