converting to date for comparing dates

Post Reply
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

converting to date for comparing dates

Post by Analytics123 »

hi , i have in my source file values like for date

6/10/2015
Is there a specific code to append 0 before 6 or a code to say if the length of month or day is 1 then append 0 in front of it .

Thanks,
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: converting to date for comparing dates

Post by Wim Gielis »

Did you spend a minute in the help on TM1 rules functions ? Long, Scan, Insrt, and so on.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: converting to date for comparing dates

Post by Analytics123 »

i know scan will help to get the '/ 'position, but is there a way to get the first and second slash position too.

Yes i know we can come up with logic, but you dont think its too much calculation , if length is 10 then the date otherwise , check if length is 8 or length is 9 .
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: converting to date for comparing dates

Post by Alan Kirk »

Analytics123 wrote: Sat Dec 09, 2017 2:54 am i know scan will help to get the '/ 'position, but is there a way to get the first and second slash position too.

Yes i know we can come up with logic, but you dont think its too much calculation , if length is 10 then the date otherwise , check if length is 8 or length is 9 .
I do. Which is why I think you should approach whoever is supplying you with this data and tell them to get their head out of their backside and start supplying it in a consistent format. It's really not a particularly arduous thing to do out of any half-decent database, or even Excel come to that.

In the meantime, in the absence of a Split function in TI (one of many annoying absences) the only thing that you can do is use the string functions that Wim mentioned to work out the split. There's no standard function to do it for you.

Bear in mind though that if your data suppliers have been kind enough to be consistent about using a 4 digit year, you can use SubSt split off the last 5 characters. That way you need find only one slash, and split that string to get day and month.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Post Reply