Find last Saturday of prior month

[severhart]

Hi 🙂 I've tried searching but all I'm finding is results to get the last day of the prior month.

Situation: reporting weekly data. I have data stored in my file with a keyfield of Week Ending day. I've been able to get a formula to extract the prior week's date and the prior year's corresponding week's date, but I need to be able to get the last one of the prior month.

ie: today is 6/17/2020

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

the above gets me 6/13/2020

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

the above gets me 6/15/2019

I'm seeking a formula that will give me 5/30/2020 (the last Saturday of the prior month) and I'm stuck. Can anyone help me get there?

[ChrisTX · Accepted solution]

See if the attached workflow gives you the desired output.

 

Chris

[severhart]

Hi Chris - I think this will get me started. I was hoping for a single formula vs a group of tools to incorporate, but I think I can adapt this to get me going. I really appreciate you taking the time to share an example!