Week VSring to Week Date format

[AntlRo]

Hello,

I have a column called week with data type V_string  as such below :

Week	Category
09-13 Jan 2023	Orange
16-20 Jan 2023	Apple

I want to convert the week column to week format as such(keep in mind the data has hundreds of rows) :

Week	Category
2	Orange
3	Apple

[DataNath · Accepted solution]

Hey @AntlRo, is your incoming week range field in a consistent format i.e. that of the 2 examples above? Could perhaps try something like this:

DateTimeFormat(DateTimeParse(Right([Week],11),'%d %b %Y'),'%W')

If you don't want the leading zeros in numbers 1-9, you can just add a TrimLeft() like so:

TrimLeft(DateTimeFormat(DateTimeParse(Right([Week],11),'%d %b %Y'),'%W'),'0')

[AntlRo · Accepted solution]

this works great now how would you split it to get two columns( the second being in date format say first day of the week) as such..

Week	Category
09-13 Jan 2023	Orange
16-20 Jan 2023	Apple

Desired Output:

new Columns:

Week	Date	Category
2	1/9/2023	Orange
3	1/16/2023	Apple

[DataNath · Accepted solution]

@AntlRo there's a bunch of ways you could go about doing this but here's one way:

[AntlRo]

Thanks!