Help with excel formula translation to find most recent friday

[hannahrakow]

I am trying to build a formula in alteryx to return the most recent Friday of todays date.. the excel formula is fairly straitforward: 

=TODAY()-MOD(WEEKDAY(TODAY(),16),7)

Could someone assist in the translation? trying to build this out in alteryx is proving to be extremely difficult..

TYIA

[DiganP]

@hannahrakow I believe it would be something this

tonumber(DateTimeFormat(DateTimeNow(),"%w"))+2

in the formula tool.

Datetimeformat() - formats the date

datetimenow() - todays date (tuesday)

%w - Day of week as a number, 0 to 6, with Sunday as 0

+2 because saturday, sunday (starts from 0)

The above gives me 4, your excel formula gives the same.

Does this work?

[hannahrakow]

Thanks for your reply! n

ot sure if formatting is issue but when I type my formula in excel it returns 10/18/19 (or whichever date is the most recent friday from today) and thats the result I am looking for in alteryx. 

[DiganP · Accepted solution]

@hannahrakow If you want the last Friday, you can write another formula:

tonumber(DateTimeFormat(DateTimeNow(),"%w"))+2 - this calculates days since last Friday

DateTimeAdd(DateTimeNow(),-[test],'days') - generates the date to the last Friday

Attached is the workflow.

[hannahrakow]

Thank you! This worked!