Formula to give next monday date from todays date

[john dalton]

Hi I have a formula

=AL260-WEEKDAY(AL260,2)+MATCH(AP260,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0)

The formula looks at the chosen start date (AL260), and then uses a displayed text day (AP20) to display the relvent date to the text day, this works fine however if the start date is say 08/08/14 (a Friday) when the formula detects a Monday (this copies down a range looking at the start date and the text days to produce dates on each line, the formula displays the Monday dates prior to start date, is there any way to alter this to make it display the dates ahead of the chosen start date?

Thnaks in advance

JD

[sktneer]

Not sure what sheet layout you have.......
But if you have start date in AL260 and you want to find the date with next Monday, try this.....

Please Login or Register  to view this content.

Does this help?

[john dalton]

Hi It needs to react to the AP260 text displayed day part, Monday, Tuesday Wed etc (only weekdays are displayed), your formula finds the monday after the friday date (08/08/14) but I need it to calculate each time a different text day is entered.

Cheers

JD

[daffodil11]

I went with:

=LOOKUP(2,1/(WEEKDAY(AL260+{1;2;3;4;5;6;7},2)=1),(AL260+{1;2;3;4;5;6;7}))

I'm not quite sure I follow why you're using both a Day of Week display, and a numerical date. Isn't the next Monday the same regardless?

[john dalton]

The days are dictated as default days for courses so any day say a monday course falls on the date shows the monday date for the first monday after the start date, I still need the formula to react to the text day and the start date.

Cheers

JD

[sktneer]

May be this........

Please Login or Register  to view this content.

[john dalton]

Cheers sktneer, that works a treat pal.

JD

[sktneer]

Glad to help you. Thanks for the feedback and rep.

[daddylonglegs]

IF AL260 is A Wednesday and AP260 says "Wednesday" do you want the result to be the same as the date in AL260 or the following week? If it's the former try this

=AL260+7-WEEKDAY(AL260-1-MATCH(AP260,TEXT({2,3,4,5,6},"dddd"),0))

for the latter try

=AL260+8-WEEKDAY(AL260-MATCH(AP260,TEXT({2,3,4,5,6},"dddd"),0))

[john dalton]

Hi daddylonglges it would be the same date.

Thanks for the reply I have implemented the suggestion made by sktneer, for some reason the thread hasn't come up as solved me IE crashed when I marked it a solved.

Thnaks to everyone who posted.

Cheers

JD

[daddylonglegs]

......it would be the same date.

Hello John, sktneer's solution will give you the following week in that case - try my first suggestion