Hi 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

Hello 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

CoEnterprise | Home

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

Hi 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

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

Hi 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

Hi Marie,

Glad to know that!!! Please mark it Helpful and CORRECT to close thread.

Thanks

Deepak