How to convert Week of Year number to a standard Date format?

[mrfredman]

Hi,

I have a set of data where the date is stored as Year/Week of Year.

For example: this week would be 14/43 while the first week of January first would be 14/1.

I would like to convert this into something easily readable, likely Month/Day/Year.

As Week of Year doesn't seem to be a standard time/date variable, I'm having trouble figuring out a solution using time conversion methods. Any pointers would be welcome.

[martin_mueller]

Assuming your week starts with a Monday, here's a pretty way of doing this:

| stats count | eval in = "2014/43" | eval out = strptime(in." 1", "%Y/%U %w") | eval formatted = strftime(out, "%+")

That yields Monday, October 27th 00:00:00 for me. Change the 1 in the strptime() to 0 if your weeks start with Sunday.

[mschellhouse]

How about if the format is YYYY-MM-WW? ex. 2017-10-41.

[martin_mueller]

Strip out the month and go from there? The week number should be sufficient.

Alternatively, add the month to the strptime.

[somesoni2]

Try something like this (runanywhere sample)

|gentimes start=-1 | eval temp="14/43" | table temp |  eval newDate=relative_time(strptime(mvindex(split(temp,"/"),0)."/01/01","%y/%m/%d") + tonumber(mvindex(split(temp,"/"),1))*86400*7 - 86400,"@w") | convert ctime(newDate)

You can use the expression into a macro (eval newDate).

[Raghav2384]

Is this based on Fiscal calendar? what i am going to suggest might be the hardest way. Here's what i think should do.
Going by your example, 14/43
i would extract it rex "^.*\/(\d+)\s". Now i should have Week field = 43
A giant if loop , Eval Month = if((Week<=5),"January",if(Week>5 AND Week <=10),February..........)
Now you have both Week and Month fields. Concatenate Year and Month.

[Raghav2384]

Sorry the rex is ended up all wrong. "^.*\/(?\d+)\s"