How to receive the same week number as is in excel ?

[JustynaMZ]

Hi,

I was trying few solutions but still did not receive the result I am looking for.

I have a report with data from few years.

We want to receive comparison week to week for each calendar year.

As previously we used excel to count number of weeks I would like to receive now the same week number as it appears in excel.

I was trying to use Week number formula, week number + 1 and 2 more 'tricks' I found here. But still do not receive the same results.

Do you have any advice what to try?

Thanks in advance for your help!

[Kenda]

Hello @JustynaMZ 

Have you tried the DateTimeParse() or DateTimeFormat() functions?

Use this webpage to get more details: https://help.alteryx.com/current/designer/datetime-functions 

[messi007]

@JustynaMZ,

Could you share some simple data with the expected result in order to help 🙂

I had some issue using datetimediff with weeks I changed to days in order to flag weeks

DateTimeDiff([EndDate],[StartDate],"days")

Hope that helps,

Regards, 

[JustynaMZ]

Application Date and Time	Week Alteryx	Week Excel	match?	I would like to receive the same Week nr in Alteryx as I receive in XLSX
01.01.2015 00:00	1	1	yes
02.01.2015 00:00	1	1	yes
03.01.2015 00:00	1	1	yes
04.01.2015 00:00	1	2	no
05.01.2015 00:00	1	2	no
06.01.2015 00:00	1	2	no
07.01.2015 00:00	1	2	no
08.01.2015 00:00	2	2	yes
09.01.2015 00:00	2	2	yes
10.01.2015 00:00	2	2	yes
11.01.2015 00:00	2	3	no
12.01.2015 00:00	2	3	no
13.01.2015 00:00	2	3	no
14.01.2015 00:00	2	3	no
15.01.2015 00:00	3	3	yes
16.01.2015 00:00	3	3	yes
17.01.2015 00:00	3	3	yes
18.01.2015 00:00	3	4	no
19.01.2015 00:00	3	4	no
20.01.2015 00:00	3	4	no
02.03.2019 21:44	9	9	yes
02.03.2019 22:30	9	9	yes
02.03.2019 22:32	9	9	yes
02.03.2019 22:34	9	9	yes
02.03.2019 22:43	9	9	yes
02.03.2019 23:25	9	9	yes
02.03.2019 23:31	9	9	yes
02.03.2019 23:31	9	9	yes
02.03.2019 23:40	9	9	yes
03.03.2019 00:08	9	10	no
03.03.2019 00:14	9	10	no
03.03.2019 00:25	9	10	no
03.03.2019 00:29	9	10	no
03.03.2019 00:34	9	10	no
03.03.2019 00:42	9	10	no
03.03.2019 01:04	9	10	no
03.03.2019 01:17	9	10	no
03.03.2019 02:57	9	10	no
03.03.2019 03:28	9	10	no
03.03.2019 03:39	9	10	no

[messi007]

@JustynaMZ,

Please see below:

Attached the workflow,

If this solves your issue please mark the answer as correct, it will help others!

Regards,

[JustynaMZ]

Thanks for your help.

Nevertheless I probably was not clear enough.

I do not need to compare Weeks if are the same as in Excel.

I have a table with column application date and time (sample above). Based on the date I want to receive 'Week Number' (the same as in column Week Excel).

Nevertheless when I use 'Datetimeformat' (Datetimeformat +1)I receive different week numbers than I received when I was using Excel in the past (WeekNum formula in excel). (that is why I presented these 2 additional 'Week Alteryx' and 'Week Excel' columns+ column which shows what are the differences to show where I see differences).

Unfortunately I cannot use 

ToNumber(DATETIMEFORMAT([Date], "%W"))+1

as it does not work for all years I have in my report.

To summarize:

1. I have a table with column 'Application Date and Time'

2. I want to receive column with 'Week Number'

 - but to be counted same way as in Excel.(as in the past we presented the data based on excel formula)

I do not know why alteryx shows different weeks than WeekNum in Excel 😞

Application Date and Time	WEEK Number?
01.01.2015 00:00
02.01.2015 00:00
03.01.2015 00:00
04.01.2015 00:00
05.01.2015 00:00
06.01.2015 00:00
07.01.2015 00:00
08.01.2015 00:00
09.01.2015 00:00
10.01.2015 00:00
11.01.2015 00:00
12.01.2015 00:00
13.01.2015 00:00
14.01.2015 00:00
15.01.2015 00:00
16.01.2015 00:00
17.01.2015 00:00
18.01.2015 00:00
19.01.2015 00:00
20.01.2015 00:00
02.03.2019 21:44
02.03.2019 22:30
02.03.2019 22:32
02.03.2019 22:34
02.03.2019 22:43
02.03.2019 23:25
02.03.2019 23:31
02.03.2019 23:31
02.03.2019 23:40
03.03.2019 00:08
03.03.2019 00:14
03.03.2019 00:25
03.03.2019 00:29
03.03.2019 00:34
03.03.2019 00:42
03.03.2019 01:04
03.03.2019 01:17
03.03.2019 02:57
03.03.2019 03:28
03.03.2019 03:39

[JustynaMZ]

Hi, maybe somebody will have the same problem.

So here is how I solved this one:

[Kenda]

Hi @JustynaMZ 

I was able to create an Alteryx week field that matched your Excel week field using the following expression:

tonumber(datetimeformat(DateTimeParse([Application Date and Time],"%d.%m.%Y %H:%M"),"%U"))+1