Convert Batch code to datetime

[winnie12]

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?

[MichalM · Accepted solution]

Hi @winnie12

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

[winnie12]

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?

[MichalM]

Of course! See the attached.

[winnie12]

Hi MichalM,

It's not working when I apply with year 2016. Kindly find attached for reference.

BR,

[winnie12]

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

[MichalM]

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?

[winnie12]

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. :-)

[MichalM · Accepted solution]

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")
THEN DateTimeAdd([FirstOfYear],Mod(1-ToNumber(DateTimeFormat([FirstOfYear],"%w")), 7), "days")
ELSE DateTimeAdd([FirstOfYear],Mod(1-ToNumber(DateTimeFormat([FirstOfYear],"%w")), 7) + 7, "days")
ENDIF

We'd be checking whether the initially generated date has the same year value as FirstOfYear (1st of Jan). If this is the case, all's good. If not we'd be adding 7 more days to the expression - highlighted in red above.

[winnie12]

Thank you very much.