Forum Discussion

MichaelTamoush's avatar
MichaelTamoush
Qrew Captain
4 years ago

Calculate a specific day of the week, before a date field

I recently needed a date field that would calculate the previous Thursday on or before a date (in a due date field). So for example, if the Due Date was a Monday, I wanted the date of the previous Thursday. No matter the due date, I wanted the date of the prior Thursday (or if the due date was a Thursday, then I want that Thursday).

I borrowed an old formula I had used in excel, that I found online and modified it to work in QB. I found it very sneaky and cool, so sharing it here. Maybe someone else will have a need for it!

For Prior Wednesday: [Due Date] - Days(Rem(DayOfWeek([Due Date])+4,7))
For Prior Thursday: [Due Date] - Days(Rem(DayOfWeek([Due Date])+3,7))
For Prior Friday: [Due Date] - Days(Rem(DayOfWeek([Due Date])+2,7))
etc.
(Sunday would be + 7, Saturday would be +1)

------------------------------
Mike Tamoush
------------------------------