2

If I have a column of dates filed like the below;

Date
2021-08-01
2021-08-02
2021-08-03
2021-08-01
2021-08-02

What I wish to do is add a new column that will tell me the number of mondays for example that the date is in the year.

so I can see that for the first record the first of August was a Sunday and it was the 31st Sunday of the year, whereas the 12th was a Thursday and was the 32nd Thursday of the year.

Date        Number Of WeekDay in Year
2021-08-01            31
2021-08-02            31
2021-08-03            31
2021-08-12            32
   ...                ...

If it makes it easier is there a way to do it using the python tool within Alteryx?

djvg
  • 11,722
  • 5
  • 72
  • 103
craig3656
  • 41
  • 4
  • 2
    Take a look at `datetime`: https://docs.python.org/3/library/datetime.html#datetime.date.weekday – Tranbi Aug 04 '21 at 11:56
  • Does this answer your question? [How do I get the day of week given a date?](https://stackoverflow.com/questions/9847213/how-do-i-get-the-day-of-week-given-a-date) – shoaib30 Aug 04 '21 at 12:15

3 Answers3

1

For Alteryx, try the formula Ceil(DateTimeFormat([date],'%j') / 7) ... explanation: regardless of day of week, if it's the first day of the year, it's also the first "of that weekday" of the year... at day number 8, it becomes the 2nd "of that weekday" of the year, and so on. Since Alteryx gives you "day of the year" for free using the given DateTimeFornat function, it's then a simple division and Ceil() function.

johnjps111
  • 1,160
  • 9
  • 24
1

The answer by johnjps111 explains it all, but here's an implementation using python only (no alteryx):

import math
from datetime import datetime

def get_weekday_occurrences(date_string):
    year_day = datetime.strptime(date_string, '%Y-%m-%d').timetuple().tm_yday
    return math.ceil(year_day / 7)

Which can be used as follows:

get_weekday_occurrences('2021-08-12')

Note we use datetime.timetuple to get the day of the year (tm_yday).

djvg
  • 11,722
  • 5
  • 72
  • 103
0

To get the week in the year from a date string:

from datetime import datetime

a = '2021-08-02'
b = datetime.fromisoformat(a)
print('week of the year:', b.strftime('%W'))

output:

week of the year: 31

For more information about datetime: link

Almog-at-Nailo
  • 1,152
  • 1
  • 4
  • 20