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,
converting to date for comparing dates
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
-
- MVP
- Posts: 3121
- 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
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
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
-
- 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
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 .
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 .
- Alan Kirk
- Site Admin
- Posts: 6608
- 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
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.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 .
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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.