Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Date Time Parse from year and week number

MikeLR
8 - Asteroid

Hi all

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

MikeLR_0-1605601201252.png

Any ideas on what I'm doing wrong please?

Thanks

Mike

6 REPLIES 6
PhilipMannering
16 - Nebula
16 - Nebula

Apparently %W is not supported for a datetimeparse()

AngelosPachis
16 - Nebula

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
ACE Emeritus
ACE Emeritus

%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
8 - Asteroid

@PhilipMannering 
Ok thanks for confirming

MikeLR
8 - Asteroid


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

MikeLR
8 - Asteroid

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

Labels