Hi Michelle - I've been told that this consumes more calc resources than your typical formulas (e.g. lookup, basic "if")

We have very good sponsorship from our leadership teams but they don't tend to get in to the day-to-day like the dataset standards. We've had Apptio for about 5 years so have built up some really good relationships, obviously we don't take advantage of this as owners as well as the data changes so we try to automate as many datasets as possible to ensure the requests are less taxing on the data owners

^that's always the best way to go about ever-changing/non-standardized data, Julie.

Do you have an executive sponsor for your TBM Implementation that helps you enforce the feedback regarding the data standards/layout that you use in Apptio?

I believe that support from up top really helps getting other groups to cooperate.

Due to encountering this issue so many times in our organisation that our model became so overly complicated just to standardise the data, we therefore decided to return data and/or work closer with our data owners to increase the usability of their data 

@Jeremy Wilson @Anthony Bush

Does ReplaceRegex impact system performance?

wow - didn't realize it could be as simple as using this syntax:

Start Date =ReplaceRegex(name,"^.*?(\d{1,4}[-/]\d{1,2}[-/]\d{1,4}).*","$1")

End Date  =ReplaceRegex(name,"^.*?(\d{1,4}[-/]\d{1,2}[-/]\d{1,4}).*?(\d{1,4}[-/]\d{1,2}[-/]\d{1,4}).*","$2")

I did a little reading up on Regular Expressions, and it looked a little different than what's being used within TBM Data Studio.

Is there any reference within the community that serves as a cheat sheet for capturing regular expressions?

And also common uses? (like this one in particular)

Also, I was wondering what the best way of extracting values for the "Description" field would be.

Very enlightened by this challenge, and I'm glad to know that Apptio R12 has this sort of function.

We're starting to go deeper into IT Concepts here, which is pretty awesome.

Thank you to everyone who took the time to submit an answer!  As always, there is more than one way to implement a solution within Apptio. As most of you have determined, what we were looking for was the the use of the ReplaceRegex() function to extract the dates from the dataset. The reason this is the most (and possibly only) viable solution within Apptio, is that we have things like dates in YYYY-MM-DD format as well as MM-DD-YYYY, dates using '-' as well as 'to' as separators, etc.

Having said this, there are still multiple ways of implementing this particular function due to the flexible nature of regular expressions, so we have decided to post the implementation we chose, as well as three other solutions that worked from the answers submitted:

Apptio Implementation: 

Start Date =ReplaceRegex(name,"^.*?(\d{1,4}[-/]\d{1,2}[-/]\d{1,4}).*","$1")

End Date  =ReplaceRegex(name,"^.*?(\d{1,4}[-/]\d{1,2}[-/]\d{1,4}).*?(\d{1,4}[-/]\d{1,2}[-/]\d{1,4}).*","$2")

Now, there is one additional step that would need to be done to format the dates, using DateFormat(), however the goal of this exercise was only to extract the dates in a valid format we can read, not to necessarily standardize them. Some of the solutions provided wrapped the regex output in a DateFormat(), which is fantastic!

Submitted Solutions that provided an accurate result in all cases (given sample data):

#1 (@Nick Brandwood)
Start Date =DateFormat(Split(ReplaceRegex(name,"([a-z]|[A-Z]|:|(\s-\s))+"," "),1," "),"MM/dd/yyyy")

End Date  =DateFormat(Split(ReplaceRegex(name,"([a-z]|[A-Z]|:|(\s-\s))+"," "),2," "),"MM/dd/yyyy")

#2 (@Josef Petzenhammer)

Start Date =DateFormat(ReplaceRegex(name,".*?(\d{1,4}).(\d{1,4}).(\d{1,4}).(\D+).*?(\d{1,4}).(\d{1,4}).(\d{1,4}).*","$1/$2/$3"),"M/d/yy")

End Date =DateFormat(ReplaceRegex(name,".*?(\d{1,4}).(\d{1,4}).(\d{1,4}).(\D+).*?(\d{1,4}).(\d{1,4}).(\d{1,4}).*","$5/$6/$7"),"M/d/yy")

#3 (@Kyle Castro)

001 SUBSTITUTE (Date Parse) =SUBSTITUTE(name," - "," ")
002 REPLACEREGEX (Date Parse) =ReplaceRegex({001 SUBSTITUTE (Date Parse)},"[a-zA-Z:]"," ") 003 Start Date Pass 1 =Split({002 REPLACEREGEX (Date Parse)},1," ")

004 End Date Pass 1 =Split({002 REPLACEREGEX (Date Parse)},2," ")

005 Start Date Final =DateFormat({003 Start Date Pass 1},"medium-date")

006 End Date Final =DateFormat({004 End Date Pass 1},"medium-date")

007 REPLACEREGEX (Description Parse) =ReplaceRegex(name,"[0-9/:-]","")

008 First Name =split({007 REPLACEREGEX (Description Parse)},1," ")

009 Last Name =split({007 REPLACEREGEX (Description Parse)},2," ") 010 Description Final =If({008 First Name}="","Undefined",CapFirstletter({008 First Name})&" "&CapFirstletter({009 Last Name}))

@Jenny Franklin Oh goodness! When are you planning to make the move? That is a big one but worth it.

Hey everyone - thank you to those that submitted an answer!  @Michael Darragh� should be posting the answer here in the comments soon.

Congratulations to those who answered correctly: @Nick Brandwood, @Josef Petzenhammer & @Kyle Castro!!  You will each be awarded a badge with points in just a moment.

I am also interested in the solution, I have sent a possible solution but have not received any feedback yet.

Is it possible to know the answer for this one?

Same here @Shannon Lynch - too much going on at the moment.  Plus, given my R11 situation, that would have been a mess of a transform lol...  So much easier w/the one function in R12!!  Can't wait to get out of R11!

I was unable....typo

@Demarcus Lloyd, you will have to let me know when the answer on this one comes out. I was able to fit this one into my schedule to submit an answer. I am super curious as I can think of a few ways but most of which utilizing multiple functions.

Gosh @Demarcus Lloyd! Couldn't give an "old dog" a break here. I see how you roll! ;-)

I gave this challenge a shot... but I can't help but feel that I've used too many formulas to arrive at a solution.

Hopefully, you still consider my submission though! *fingers crossed*

Edit: I've submitted a 2nd attempt at this using that "one function", too... I'm still a noob at using it, so it's pretty tricky to use!

Spent some more brain power on it after reading  your hint with the "One Formula" solution.

Looking forward to hear your opinion on this new version!

@Jenny Franklin - my thoughts exactly   

I still think it is possible in R11... just wont be pretty...

Ah, but that "one function" isn't available in R11, so this is going to be fun... sarcasm... sarcasm...

Tried my luck with some String juggeling - let's see what you think about it @Demarcus Lloyd @Andrew Mulvaney @Michael Darragh�

Be gentle, just started with Apptio last week