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?
Solved! Go to Solution.
Hi @winnie12
Would the attached work?
Here's what I've done
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
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?
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?
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. :-)
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.
Thank you very much.