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.