Date Time Parse from year and week number

[MikeLR]

Hi all

I'm slightly surprised that this formula gives -01-01 as the day and month for all weeks.

Any ideas on what I'm doing wrong please?

Thanks

Mike

[PhilipMannering · Accepted solution]

Apparently %W is not supported for a datetimeparse()

[AngelosPachis · Accepted solution]

Hi @MikeLR ,

I don't think you are doing something wrong, it's just that the "%W" specifier is not supported with the datetimeparse function.

You can find a nice solution to that problem by @DavidP in the following post

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Week-Number-to-Date-Formula/td-p/56940...

Hope that helps.

Regards,

Angelos

[jdunkerley79 · Accepted solution]

%W can only be used in DateTimeFormat not in DateTimeParse. 

Something like:

DateTimeAdd(
DateTimeAdd(LEFT([Fiscal Week Number],4) + '-01-01', 
            -ToNumber(DateTimeFormat(LEFT([Fiscal Week Number],4) + '-01-01', '%w')), 
            "days"),
 7 * ToNumber(Right([Fiscal Week Number],2)), "days")

should be close to what you need.

[MikeLR]

@PhilipMannering 
Ok thanks for confirming

[MikeLR]

@AngelosPachis Thanks for the link, that's a useful solution

[MikeLR]

@jdunkerley79 That works a treat thanks. Now I just need to make sure I understand exactly what it's doing 🙂