Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Date conversion to Day of Week

BarleyCorn
8 - Asteroid

Trying to convert a date to day of the week. So field of actual date to new variable that is Monday, Tuesday, Wednesday, etc... based on the field date. Have tried DateTimeParse and DateTimeFormat functions in Formula Tool - no luck!

 

This is a basic excel command????

 

Thanks

 

14 REPLIES 14
JohnJPS
15 - Aurora

Hi @BarleyCorn,

 

Check out the list of DateTimeFormats here: https://help.alteryx.com/9.5/index.htm#Reference/DateTimeFunctions.htm

 

From there, you can find the correct format to apply:

DateTimeFormat([DateField],"%A")

 

BarleyCorn
8 - Asteroid

Thanks John,

 

Worked, but also stung by Alteryx default on double and string!!

 

jchadwick
5 - Atom

I'm looking for a formula to return the Friday of a due date.  I want the actual date.

 

I'm considering using a long nested if statement such that: (syntax to be corrected)

if DateTimeFormat([DueDate],"%A") = "Sat" add 6 days

if DateTimeFormat([DueDate],"%A") = "Sun" add 5 days

if DateTimeFormat([DueDate],"%A") = "Mon" add 4 days

if DateTimeFormat([DueDate],"%A") = "Tue" add 3 days

if DateTimeFormat([DueDate],"%A") = "Wed" add 2 days

if DateTimeFormat([DueDate],"%A") = "Thu" add 1 days

if DateTimeFormat([DueDate],"%A") = "Fri" add 0 days

 

Elsewhere I've used a WeekDay formula to return the day of week as a number, then math into the Friday.  For example:

DueDate - WeekDay(DueDate) + 7 ... always returns the Friday date.

 

I welcome your guidance.

 

JohnJPS
15 - Aurora

Hi @jchadwick,

Good idea.  The following would be a nice one-liner similar to yours:

DateTimeAdd([DateField],5-ToNumber(DateTimeFormat([DateField],"%w")),"days")

 

stephenweiss0
5 - Atom

I think you meant `%a` not `%A` --

 

Per https://help.alteryx.com/9.5/Reference/DateTimeFunctions.htm, 

 

%a = "Mon", "Tue", etc.

%A = "Monday", "Tuesday", etc.

 

Both work, but the former makes sense in the context of your conditionals. 

danejensen4
5 - Atom

I didn't find anything on Alteryx's website about this, but I stumbled upon a possible solution.  DateTimeFormat([DateField],"%u") will give you the day of the week where Monday = 1 and Sunday = 7.

carl_steinhilber
8 - Asteroid

@danejensen4 wrote:

I didn't find anything on Alteryx's website about this, but I stumbled upon a possible solution.  DateTimeFormat([DateField],"%u") will give you the day of the week where Monday = 1 and Sunday = 7.


This NEEDS to be in the documentation!

 

Thanks @danejensen4 !

wolnov
5 - Atom

Thanks, @danejensen! This seems to work well. I have used ToNumber(DateTimeFormat([DateField],"%u")) to convert the field into number

MatthieuArzel
7 - Meteor

If it's any help you can also use DateTimeFormat([Date],"%w") 

where sunday = 0 and saturday = 6

Labels