Forum Discussion
Solved
Calculate "The Wednesday" two weeks before due date
We currently run checks on Wednesday of each week. To show an "anticipated pay date", I want to somehow use the "due date" of the invoice and return the date two Wednesday's prior.
Is that even possible?
For example, if the due date is Thursday, May 1st, I want it to return Wednesday, April 23rd.
If the due date was Tuesday, April 29th or Wednesday, April 30th, I want it to return Wednesday, April 16th.
I know how to return "two weeks ago", but since I only want Wednesday's that makes it a little more confusing. Is there a "2 (day of the week) ago" -type formula?
The WEEKDAY function can be used for this. Its first argument must be a date value, of course. It returns a number from 1 to 7 indicating the day of the week. It also allows for a second argument (useful in this calculation) where you can specify which day of the week is assigned 1 (the default is Sunday).
So the formula can be:
=A2 - WEEKDAY(A2,14) - 7where A2 is a due date. See the attached workbook for more info and sample calculations.
SergeiBaklanSnowMan55BOTH of these solutions worked wonderfully. Thank you so much
Just in case, that could be
=A2 - MOD( A2-5, 7 ) - 8The WEEKDAY function can be used for this. Its first argument must be a date value, of course. It returns a number from 1 to 7 indicating the day of the week. It also allows for a second argument (useful in this calculation) where you can specify which day of the week is assigned 1 (the default is Sunday).
So the formula can be:
=A2 - WEEKDAY(A2,14) - 7where A2 is a due date. See the attached workbook for more info and sample calculations.
