Date conversion to Day of Week
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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")
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks John,
Worked, but also stung by Alteryx default on double and string!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @jchadwick,
Good idea. The following would be a nice one-liner similar to yours:
DateTimeAdd([DateField],5-ToNumber(DateTimeFormat([DateField],"%w")),"days")
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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 !
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks, @danejensen! This seems to work well. I have used ToNumber(DateTimeFormat([DateField],"%u")) to convert the field into number
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
If it's any help you can also use DateTimeFormat([Date],"%w")
where sunday = 0 and saturday = 6
