find the Monday or Wednsday before a date in excel

excel date dayofweek
jpolache picture jpolache · May 13, 2013 · Viewed 7.1k times · Source

I have a list of assignments in excel 2010. Each has a due date. The assignment must be submitted for external processing 3 working days before the due date. Before the assignment can be sent to external processing, it must be reviewed. Submissions for review are on Mondays and Wednesday.

I want a function that looks at the date in the due date cell and returns the date of the Monday or Wednesday (which ever is closer) before the date 3 workdays before that date;

X = (3 workdays before the due date)

submit date = (Monday or Wednesday before X)

I got X thus; =WORKDAY.INTL(<due date cell>,-3)

Now I just need the code to get to the submit date.

Answer

Scheballs picture Scheballs · May 13, 2013

Ok, I tried to do this without a huge IF() statement but this works. Your date is in A1, and your Submit date formula below is in B1.

=IF(WEEKDAY(A1)=1,A1-6,IF(WEEKDAY(A1)=2,A1-7,IF(WEEKDAY(A1)=3,A1-6,IF(WEEKDAY(A1)=4,A1-7,IF(WEEKDAY(A1)=5,A1-8,IF(WEEKDAY(A1)=6,A1-4,IF(WEEKDAY(A1)=7,A1-5)))))))

Pretty much what I did was I figured out how many days to subtract from each due date when you subtracted at least 3 workdays and then subtracted more days until you got to a Wed or Mon. When your...

  • Due Date; then (Days to subtract to get desired Mon or Wed Submit Date)
  • Sunday; then -6
  • Monday; then -7
  • Tuesday; then -6
  • Wednesday; then -7
  • Thursday; then -8
  • Friday; then -4
  • Saturday; then -5

Hope this helps.

-Scheballs

Has to be Mon or Wed

EDIT

Thanks to Barry Houdini for showing me the CHOOSE() function.

=A2-CHOOSE(WEEKDAY(A2),6,7,6,7,8,4,5)

Pretty much once you have found the offsets needed for the days to subtract from your day of the week you can use the WEEKDAY value as the position in a list of options. So if our weekday due date is Sunday, that is a "1" and then our choose function list of options, the first one is chosen. Then our Due date is subtracted by that value. Very Nice Barry, thanks.

Related questions

How to add number of days to a date, consider only business days (i.e. ignore weekends)?

I'm trying to create a formula to calculate the n-th Business Date (only Monday to Friday are business days). For simplicity's sake, holidays are not important; only weekends should be ignored. For example: +------------------------------------------------------------------ | A B C +------------------------------------------------------------------ 1 | Starting Date …

Read More
Count number of times a date occurs and make a graph out of it

I have a list of dates, each date in it can occur more than once. I want to count the number of times each date occurs (histogram) and display it in a graph (with the Y axis being the number …

Read More
Formula to convert date to number

I would like to know the formula to convert a date in 10/26/2013 to 41573 number as done in Excel. Like how 10/26/2013 is converted to 41573.

Read More