-
1. Re: How can I convert string to date?
Jim DehnerMar 6, 2017 12:56 PM (in response to Marie Hamel)
1 of 1 people found this helpfulHi Marie
First split your string into year and quarter -'20'+ Split(datestring,'-',1) for year and Split(datestring,'-',2) for quarter
Then you need to decide on a date for Q1 , Q2 etc - something like>> if quarter = Q1 then '01' elseif quarter =q1 then '04' ...etc
then you use >>> Makedate(Year,Month,'01')
Let me know if that helped
JIm
-
2. Re: How can I convert string to date?
Lisa Li Mar 6, 2017 1:09 PM (in response to Marie Hamel)1 of 1 people found this helpfulHello Marie,
You can utilize the MAKEDATE(year, month, day) function in a similar calculation as follows:
MAKEDATE (
//year
2000 + INT((LEFT([Date],2))) ,
//month
( if RIGHT([Date],2)="Q1" then 1
elseif RIGHT([Date],2)="Q2" then 4
elseif RIGHT([Date],2)="Q3" then 7
elseif RIGHT([Date],2)="Q4" then 10
end),
//day
1)
Hope this helps!
-Lisa
-
3. Re: How can I convert string to date?
Deepak Rai Mar 6, 2017 1:25 PM (in response to Marie Hamel)Hi Marie,
Here is an alternative approach to get this: Workbook Attached
I split your date as
Then wrote these 2 calculations to get what you need.
Calculation 1
IF[Date - Split 1]=1
Then
1
ELSEIF
[Date - Split 1]=2
THEN
4
ELSEIF
[Date - Split 1]=3
THEN
7
ELSE
10
END
Calculation 2
STR([Calculation1])+"/"+"01/"+LEFT([Date],2)
Hope it Helps.
Let me know
Thanks
Deepak
-
String to date.twbx 9.0 KB
-
-
4. Re: How can I convert string to date?
Gourav Sharma Mar 7, 2017 2:03 AM (in response to Deepak Rai)1 of 1 people found this helpfulHi Deepak,
The solution above seems great but this would return String value and we need to use the DATEPARSE to convert the same to date.
Gourav
-
5. Re: How can I convert string to date?
Deepak Rai Mar 7, 2017 4:20 AM (in response to Gourav Sharma)blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } No Gaurav...Simply change the data type to Date and see what happens. I did that after posting the answer. You will get new dates as 1/1/2016, 4/1/2016 etcThanksDeepak
Sent from Yahoo Mail for iPhone
-
6. Re: How can I convert string to date?
Deepak Rai Mar 7, 2017 4:30 AM (in response to Gourav Sharma)1 of 1 people found this helpfulHi Gaurav,
Check the screen shot below, I just changed Datatype to Date from String for Calculation2. There i no need to use DATEPARSE.
Thanks
Deepak
-
7. Re: How can I convert string to date?
Marie Hamel Mar 8, 2017 12:24 PM (in response to Deepak Rai)It worked!
Thank you Deepak!
-
8. Re: How can I convert string to date?
Deepak Rai Mar 8, 2017 12:26 PM (in response to Marie Hamel)1 of 1 people found this helpfulHi Marie,
Glad to know that!!! Please mark it Helpful and CORRECT to close thread.
Thanks
Deepak
-
9. Re: How can I convert string to date?
Marie Hamel Mar 8, 2017 12:30 PM (in response to Lisa Li)I appreciate your feedback. Your advise worked for me as well.
Thank you Lisa!