MichaelTamoush
4 years agoQrew Captain
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
------------------------------
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
------------------------------