9 Replies Latest reply on Jul 13, 2016 7:06 AM by Phani Kosanam

# Convert string to Day/Date Value

Hi,

My datasource has a string value for Days of the week eg:

1st Sunday

1st Monday

1st Tuesday

...

...

5th Sunday

5th Monday

5th Tuesday

I am looking to convert these string values to actual days based on current day

Eg: If my data has 10 rows for 2nd Monday, this should show the date as 7/11/2016 for current date. For next month the date will be 8/8/2016 for these 10 rows.

I was able to achieve the reverse based on current day, but note able to parse based on the above string values.

• ###### 1. Re: Convert string to Day/Date Value

Hello Phani,

I created three calculation fields to get the date.

[offset day]

CASE RIGHT([date], LEN([date])-FIND([date]," "))

WHEN "Monday" THEN 1

WHEN "Tuesday" THEN 2

WHEN "Wednesday" THEN 3

WHEN "Thursday" THEN 4

WHEN "Friday" THEN 5

WHEN "Saturday" THEN 6

WHEN "Sunday" THEN 7

END

[offset week]

INT(LEFT([date], FIND([date]," ")-3))

[date2]

The result looks like,

Regards.

2 of 2 people found this helpful
• ###### 2. Re: Convert string to Day/Date Value

Why do 5th dates go to June?

• ###### 3. Re: Convert string to Day/Date Value

Hello Dmitry,

Not sure if I've understood the 1st and 5th correctly.

In my calculation, I assumed that, these number were week offset back into the past.

Regards.

• ###### 4. Re: Convert string to Day/Date Value

The offset appears to be just different with this calculation

For July 2016 (based on current date), following would be how I would like to see...

July 1 - 1st Fri

July 2 - 1st Sat

July 3 - 1st Sun

July 4 - 1st Mon

July 5 - 1st Tue

July 6 - 1st Wed

July 7 - 1st Thu

July 8 - 2nd Fri

...

...

July 30 - 5th Sat

July 31 - 5th Sun

• ###### 5. Re: Convert string to Day/Date Value

Well, that's what you get for not clearly defined question ;)

I read it as fixed days of the week within the current month.

5th Friday is 7/29

5th Sunday is 7/31

5th Monday is either 8/1 or Undefined

Direction is correct though - parse out day of the week and week offset and do some math.

1 of 1 people found this helpful
• ###### 6. Re: Convert string to Day/Date Value

Hello Phani,

Then, with [offset week] and [offset day] not changed,

I created another 3 calculation fields.

[start]

DATE(DATETRUNC("week",

DATETRUNC("month",TODAY())))

[date2]

[date3]

date3 is the final date.

Regards.

1 of 1 people found this helpful
• ###### 7. Re: Convert string to Day/Date Value

Still got some issues with the offset.

Looks like depending on the first day calculation, the week# is being assigned. But what I don't understand is .. it works for 1st week... but the 2nd week is off because the 1st day was in 1st week that spawned 2 calendar weeks. I think we need to take the offset day to determine its placement in the week. I will try and see if I can find the correct offset.

• ###### 8. Re: Convert string to Day/Date Value

Hello Phani,

I modified the calculation.

[date3]

DATE(IIF(MONTH(

and added another one to eliminate the not existing 5th xxx.

[date final]

IF MONTH([date3])=MONTH(TODAY()) THEN [date3] END

The result becomes like this.

At last, add a filter to exclude null values for [date final].

Regards.

1 of 1 people found this helpful
• ###### 9. Re: Convert string to Day/Date Value

Great. With the updated calculation I now see correct data.