How to get prior Thursday of a specified date

Question

I'm working on a calendar week that starts on Thursday, not Sunday, and it's causing me a few fits that I hope someone can assist me with.

How do I take a date from a date that was entered into a field and always find the prior Thursday or what I am seeing as the beginning of the week and put that into a calculated column of type date time, date only?

I have tried this and a few other things for the calculated column.

 =[Start Date]-WEEKDAY([Start Date],3)+4

This goes back to the past Sunday and adds 4 to make it Thursday. That's fine, but suppose the Start Date is on a Wed?

It will return the upcoming Thursday, but I obviously want the past Thursday. Note, that I set the general SP date settings to have weeks starting Thursday, and my date pickers reflect this.

If I use -4 instead of +4 or whatever it takes to go backwards to the Thursday before Sunday, it will give me the Thursday from the prior week instead of the current one if the day is, for example, Friday of the current week.

So while I want to know the Thursday right before that Friday, it will instead give me last week's Thursday

So does anybody know of a correct, reliable way to do this where it always returns the prior Thursday or returns itself if the date in question is on a Thursday, and then returns the value in a date format of mm/dd/yyyy rather than the numeric value that the WEEKDAY function seems to return?

Thanks in advance.

Answer 1

You could brute force it. Create a calculated field and have a different test for each possible day of the week. Return the field as a Date/Time. To make sure you get it right, you can have a calculated field that just returns the weekday of the [Start Date] so you make sure you subtract out the correct number of days.

=[Start Date]+IF(WEEKDAY([Start Date])=1,-3, 
IF(WEEKDAY([Start Date])=2,-4, 
IF(WEEKDAY([Start Date])=3,-5, 
IF(WEEKDAY([Start Date])=4,-6, 
IF(WEEKDAY([Start Date])=5,0, 
IF(WEEKDAY([Start Date])=6,-1, 
IF(WEEKDAY([Start Date])=7,-2, 0 
)))))))