4

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.

jpolache
  • 305
  • 3
  • 12

2 Answers2

2

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.

Scheballs
  • 532
  • 3
  • 14
2

If your due date is a Monday then 3 workdays before that is the previous Wednesday then can you have the review on the Wednesday or does it need to be the Monday before that? If it's the latter then you can use WORKDAY and WORKDAY.INTL like this assuming due date in A2

=WORKDAY.INTL(WORKDAY(A2,-3),-1,"0101111")

If it's the former then just make the -3 into -2

With this approach you use WORKDAY to go back 2 or 3 workdays and then WORKDAY.INTL uses "0101111" to indicate Mon and Wed working days and subtracts a further day on that basis

This method can be used for any number of days - the latter number always remains -1

barry houdini
  • 45,615
  • 8
  • 63
  • 81