cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
###### #SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

## Assigning Date Ranges a Value

Asteroid

Hello - I'm looking to group line items based off of range of dates.  Specifically, I have one Date column (format '3/18/2019') and would like to create a new column with week values based off of specific ranges in the Date column.  The new column's dimensions would follow:

• 3/4 - 3/10 = "Week 1"
• 3/11 - 3/17 = "Week 2"
• 3/18 - 3/24 = "Week 3"
• 3/25 - 3/31 = Week 4"
• and so on through "Week 12"

Thank you!

Highlighted
Magnetar

Hi @navypoint16 ,

You can try this formula on your date column (create a new column called "Week" and data type is Int16):

tonumber(DateTimeFormat(DateTimeParse([Date],"%m/%d/%Y"),"%W"))-8

- The DateTimeParse function converts the date into date format

- The DateTimeFormat function then converts that date into the week of the whole year (%W specifies that a week starts on Monday)

- Tonumber converts the string of the week number into an actual number

- Since March 4th is in the 9th week of the year, we subtract 8 to get 1

This should work well into the future, past the initial 12 weeks. Let me know if this solves your challenge.

Cheers!

Esther

Asteroid

Hi Esther,

Thanks - this is very helpful.  When I created a new column with data type Int16, however, the result was "-8" for every row of data.  Do you know why this might be happening?

Thanks!

Magnetar

Would you please copy and paste the formula you're using so I can troubleshoot? My guess is something off in the DateTimeFormat and DateTimeParse

Cheers!

Esther

Asteroid

Yes - please see below:

tonumber(DateTimeFormat(DateTimeParse([Target / Actual End],"%m/%d/%Y"),"%W"))-8

Is this enough to help troubleshoot?  The new field data type is Int16 and the incoming date field (Target / Actual End) is Date.

Thanks!

Magnetar

If the incoming field is already a date, then you don't need the DateTimeParse. Your post showed a format that Alteryx wouldn't understand to be a date ;)

tonumber(DateTimeFormat([Target / Actual End],"%W"))-8

Cheers!

Esther

Asteroid

Sorry, Esther! One more question..

I had a date in 2020 (2/1/2020) that was assigned a value of -4.  The formula does not seem to take into account the year.  Can you think of any workarounds to account for this?  The value should be 50, rather than -4.

Thank you!

Labels