Formula to find the next Monday, Wednesday or Friday?

herbertlikesherbert

New Member
Joined
Feb 24, 2014
Messages
30
So I have a set date, let's say 17/08/2014, and I want the cell next to it to return the next Monday, Wednesday or Friday depending on which is next. Obviously it would return the 18/08/2014.

Then if the date was 18/08/2014 (or a Monday or Tuesday) I'd need it to find the next Wednesday, and so on for Friday.

Is there any way to do this?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
You can start with a simple formula like this:

=A1-WEEKDAY(A1-1)

That will always give you the latest Sunday before A1. That works because when A1 is a Sunday the weekday part is = 7 so you get the previous Sunday, when A1 is a Monday the weekday part is equal to 1 so you get the Sunday before, when A1 is a Tuesday the weekday part is equal to 2 so you get the Sunday before….etc. etc,

….so if you add 8, i.e.

=A1+8-WEEKDAY(A1-1)

That will always give you a Monday, the first one after A1

And you can make that a Tuesday, Wednesday etc by simpy changing the -1 to -2, -3 etc.
…so when you use an “array constant” like this:

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

That gives you an array consisting of 3 dates, the next Monday, next Wednesday and next Friday, so using MIN function around that simply gives you the first of those.

Of course that means that this function

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

Gives the same result as this

=WORKDAY(A1,1)

......and that leads you on to WORKDAY.INTL function, which allows you to choose the "working days". If you have Excel 2010 you can use that function to more easily give you the next Monday, Wednesday or Friday, i.e.

=WORKDAY.INTL(A1,1,"0101011")

where the zeroes in "0101011" represent your chosen days
 
Last edited:
Upvote 0
Ahhhh sweet! I actually have 2013 so the Workday.Intl function works as well, and actually a little bit better for me because I am using a separate column to put in the days (i.e one cell might have 0101011 and the next might have 1010111) which is useful. I wasn't able to do that the other way, but at least now I understand the maths behind it.

I just wish you could have 0101011 denoted as MWF or some variant, but I can make do without!

Thanks for your help!
 
Upvote 0
That's right yeah, and yeah Saturday and Sunday would be included. I don't mind a complex formula at all. I was just toying right now of ways to make it so i could write M,W,F in one column and have the column with the formula work it out that way, but it's proving quite difficult.

EDIT: and yeah, Tu, Th and Sa, Su were what I was thinking

EDIT2: The thing I'm struggling with is that there are so many combinations of 7 numbers, though I think that maybe trying to use IF statements isn't the best way. =IF("M,W,F", "0101011") etc

EDIT3: Okay, so I think I'm along the right lines by looking at using 1111111 as the base and subtracting numbers based on days. For example =TEXT(1111111-100, 0000000) would give me the result for the next friday
 
Last edited:
Upvote 0
Assuming date in A1 and the days to allow in B1, e.g. MWF or M, W, F then this formula in C1 should give you the correct date

=WORKDAY.INTL(A1,1,TEXT(SUM(ISERR(FIND({"M","Tu","W","Th","F","Sa","Su"},B1))*10^{6,5,4,3,2,1,0}),"0000000"))

If B1 is blank then you get an error so you might want to add another IF or IFERROR function to deal with that.....

Note that FIND makes the formula case-sensitive so you need to use W not w and Sa not SA or sa. If you don't want it to be case-sensitive just replace FIND in the formula with SEARCH

The order isn't important so W, F, M or MFW or MWF will all give the same result
 
Upvote 0
That's incredible!

I ended up spending ages working on a way to get something that I wanted. Ended up using a formula based on whether a cell was populated or not. And ended up with this:

Code:
=WORKDAY.INTL(H8,1,IF(LEN(#REF!), 0,1)&IF(LEN(#REF!),0, 1)&IF(LEN(#REF!),0, 1)&IF(LEN(#REF!),0, 1)&IF(LEN(#REF!),0, 1)&IF(LEN(#REF!),0, 1)&IF(LEN(#REF!),0, 1))

Which looks like this http://i.imgur.com/1BBmUwQ.png?1

But I much prefer your way, so I will try and incorporate that into it. I don't know how you think of these things!
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top