Fetching a specific date from a week number

[DOts]

Hello all,

I need the number of the previous week, which I get with the formula:

ToNumber(DateTimeFormat(DateTimeNow(),'%W'))

This, in the case, returns 42. But I also need what date it was Friday the previous week (like in this case of week 42, I need to fetch the date: 16/10/2020).

Could you, please, help with how to fetch the date of Friday the previous week?

[BrandonB]

You can create a new column called previous Friday and use

DateTimeAdd(DateTimeNow(),
Switch(DateTimeFormat(DateTimeNow(),'%a'),0,
'Sat',-1,
'Sun',-2,
'Mon',-3,
'Tue',-4,
'Wed',-5,
'Thu',-6,
'Fri',-7),
"days")

[CharlieS]

Hi @DOts 

I'm sure we'll see other ways to do this, but I took the current week, went one week back, generated 7 days and filtered to the Friday in those results. Check out the attached workflow and let me know if you have any questions. 

Edit: with only 7 arguments, @BrandonB's expression is far more simple. Use that. 

[RolandSchubert · Accepted solution]

Hi @DOts,

the formula:

DateTimeAdd(DateTimeNow(), -(ToNumber(DateTimeFormat(DateTimeNow(), '%w')) + 2), 'days')

should give the date of Friday last week. DateTimeFormat(DateTimeNow(), '%w') returns the number of the weekday (for Wednesday it would be 3), using DataTimeAdd you can find Friday.

Let me know if it works for you.

Best,

Roland

[DOts]

Thank you very much, guys! All solutions work fine. 🙂  I picked the most straightforward one.

[DOts]

Thank you very much, Roland - it works totally fine! 🙂