We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.
alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## Convert Batch code to datetime

7 - Meteor

I've production batch code and would to convert to date time.

EX  Batch code =180212

18 mean Year 2018

02 mean week 02 of Year 2018

1  mean monday of week 2 on Year 2018

2 mean shift time

180212 is 08/01/2018

How could i do it?

9 REPLIES 9
Alteryx

Would the attached work?

Here's what I've done

1. Turn the Batchcode into a string so that I can use string function to separate the individual elements from it
2. Drop the shift time detail as it's not needed
3. Use a regular expression to split the remaining information into three groups - year, week and day numbers
4. I then use DateTimeParse to turn the short year to 1st of January that year
5. Using the formula tool I calculate

A. First day of Week 1 of the year from the batch code

`DateTimeAdd([FirstOfYear],Mod(1-ToNumber(DateTimeFormat([FirstOfYear],"%w")), 7), "days")`

B Using A, we can calculate the first day of the week from the batch code

`DateTimeAdd([FirstDayOfWeek1], (ToNumber([WeekNo]) - 1) * 7, "days")`

C Using B, we can calculate the date - adding number of days -1 to the first day of the week

`DateTimeFormat(DateTimeAdd([FirstDayOfWeekNo],ToNumber([DayNo])-1,"days"),"%Y-%m-%d")`

Hope that makes sense.

Michal

7 - Meteor

I can't open you file due to my company don't have upgrade program. I still use license version 11.5 could you help to save as lower version or capture your workflow as picture?

Alteryx

Of course! See the attached.

7 - Meteor

7 - Meteor

May be because of week count of ours company difference from std. I've attached calendar Y16 to you in attached.

Alteryx

That's what I suspected. Is it safe to assume that Week 1 is defined as a week days of which all fall into the same year and not the week of 1st of January?

7 - Meteor

Mostly week 1 should be first week of the year. So, I can use your formulation and do some adjust if the first week not fall into the same year. thank you very much. You can help me a lot. :-)

Alteryx

Exactly! What I'd probably do is update the FirstDayOfWeek1 formula and build it out as a conditional

```If DateTimeFormat([FirstOfYear], "%Y") = DateTimeFormat(DateTimeAdd([FirstOfYear],Mod(1-ToNumber(DateTimeFormat([FirstOfYear],"%w")), 7), "days"), "%Y")