Formula to find the next Monday, Wednesday or Friday?

[herbertlikesherbert]

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?

 

[herbertlikesherbert]

Amazing, if you don't mind explaining why it works I'd love to hear, however if you are too busy I can try and work it out myself!

Thanks again!

 

[barry houdini]

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

 

[herbertlikesherbert]

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!

 

[barry houdini]

I just wish you could have 0101011 denoted as MWF or some variant......

You can probably achieve that if you don't mind the formula being a little more complex - would Saturday or Sunday ever be included? How do you distinguish Tuesday from Thursday, is Tu and Th acceptable?

 

[herbertlikesherbert]

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

 

[barry houdini]

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

 

[herbertlikesherbert]

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:

=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!