Formula to find the next Monday, Wednesday or Friday?

O

Oeldere

Well-known Member

Joined

Dec 29, 2011

Messages

2,213

• Aug 18, 2014

• #2

column B is formatted as dddd

A B C D E F G
22-08-2014 vrijdag 25-08-2014 4 2
23-08-2014 zaterdag 25-08-2014 5 1
24-08-2014 zondag 25-08-2014
25-08-2014 maandag 27-08-2014
26-08-2014 dinsdag 27-08-2014



c1=

Code:

A1+VLookup(WEEKDAY(A1),$F$1:$G$7,2,0)

and drag down

See the file on this free dutch site (no registration needed)

http://www.mijnbestand.nl/Bestand-68Y6GEONGVB8.xls

 

Last edited: Aug 18, 2014

Upvote 0

S

Special-K99

Well-known Member

Joined

Nov 7, 2006

Messages

8,508

Office Version

1. 2019

• Aug 18, 2014

• #3

Does this work?

=A1+LOOKUP(WEEKDAY(A1),{0,1,2,3,4,5,6},{1,2,1,2,1,3,2})

where A1 is the date

 

Upvote 0

H

herbertlikesherbert

New Member

Joined

Feb 24, 2014

Messages

30

• Aug 18, 2014

• #4

Special-K yours for me returns Tuesday, Thursday and Sunday, though I think I should be able to work out a simple fix. What do these {} brackets do?

Oeldere, that seems a bit complicated, downloaded the excel file, what do the numbers to the right do?

 

Upvote 0

O

Oeldere

Well-known Member

Joined

Dec 29, 2011

Messages

2,213

• Aug 18, 2014

• #5

@herbertlikesherbert

it is a table which will tell which day (6 = friday, 7 is saterday) it is.

the second column add the days (1,2 or 3) to the date.

I use VLookup to find the dates which has to be added.

Hope I explained well (enough).

 

Upvote 0

mikerickson

MrExcel MVP

Joined

Jan 15, 2007

Messages

24,355

• Aug 18, 2014

• #6

Perhaps

=A1+CHOOSE(1+MOD(A1,7), 1,2,2,1,2,1,3)

 

Upvote 0

H

herbertlikesherbert

New Member

Joined

Feb 24, 2014

Messages

30

• Aug 18, 2014

• #7

mikerickson said:

Perhaps

=A1+CHOOSE(1+MOD(A1,7), 1,2,2,1,2,1,3)

Click to expand...

Hi Mike,

Why does that work?

EDIT: Sorry, for a saturday, it gives Sunday as a result

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Aug 18, 2014

• #8

Try this version

=MIN(A1+8-WEEKDAY(A1-{1,3,5}))

....or this one

=A1+CHOOSE(WEEKDAY(A1),1,2,1,2,1,3,2)

 

Upvote 0

H

herbertlikesherbert

New Member

Joined

Feb 24, 2014

Messages

30

• Aug 18, 2014

• #9

That works perfectly... does the 1,3,5 refer to M,W,F? If I were to change it to {2,4} would it give me just Tuesdays and Thursdays?

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Aug 18, 2014

• #10

herbertlikesherbert said:

That works perfectly... does the 1,3,5 refer to M,W,F? If I were to change it to {2,4} would it give me just Tuesdays and Thursdays?

Click to expand...

Yes, that's right, so it's completely flexible, you can use any combination of days

 

Upvote 0