Need formula to return next Monday's date
 

I'm trying to return the mm/dd/yyyy for the next Monday on a continuing
basis. On a separate sheet I created column "A" with =TODAY() in cell A2
and filled down a series of the next six days. B2:B8 cells equal the
corresponding values from A2:A8 formated as dddd to display a continuously
rotating list of the days of the week.

I want to look up the text "Monday" from column B and return the
corresponding date value from column A. How can I use TEXT() in the
=INDEX(Sheet1!A2:B8,MATCH("Monday",Sheet1!B2:B8,0) ,2) function to make this
work? I'm getting #N/A.

Is there an easier way?

I'm using Excel 2007

Thanks, Frustrated in Portland

Need formula to return next Monday's date
 

=TEXT(INDEX(Sheet1!A2:B8,MATCH("Monday",Sheet1!B2: B8,0),1),"mm/dd/yyyy")

--
Tim Zych
www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
A free, powerful, flexible Excel utility


"Frustrated in Portland" <Frustrated in
wrote in message ...
I'm trying to return the mm/dd/yyyy for the next Monday on a continuing
basis. On a separate sheet I created column "A" with =TODAY() in cell A2
and filled down a series of the next six days. B2:B8 cells equal the
corresponding values from A2:A8 formated as dddd to display a continuously
rotating list of the days of the week.

I want to look up the text "Monday" from column B and return the
corresponding date value from column A. How can I use TEXT() in the
=INDEX(Sheet1!A2:B8,MATCH("Monday",Sheet1!B2:B8,0) ,2) function to make
this
work? I'm getting #N/A.

Is there an easier way?

I'm using Excel 2007

Thanks, Frustrated in Portland

Need formula to return next Monday's date
 

Is there an easier way?

So, if today is Monday then you still want next Monday's date?

=TODAY()-WEEKDAY(NOW(),2)+8


--
Biff
Microsoft Excel MVP


"Frustrated in Portland" <Frustrated in
wrote in message ...
I'm trying to return the mm/dd/yyyy for the next Monday on a continuing
basis. On a separate sheet I created column "A" with =TODAY() in cell A2
and filled down a series of the next six days. B2:B8 cells equal the
corresponding values from A2:A8 formated as dddd to display a continuously
rotating list of the days of the week.

I want to look up the text "Monday" from column B and return the
corresponding date value from column A. How can I use TEXT() in the
=INDEX(Sheet1!A2:B8,MATCH("Monday",Sheet1!B2:B8,0) ,2) function to make
this
work? I'm getting #N/A.

Is there an easier way?

I'm using Excel 2007

Thanks, Frustrated in Portland

Need formula to return next Monday's date
 

Thanks for the helpful replies.

It works!

"No longer frustrated in Portland"

"T. Valko" wrote:

Is there an easier way?

So, if today is Monday then you still want next Monday's date?

=TODAY()-WEEKDAY(NOW(),2)+8


--
Biff
Microsoft Excel MVP


"Frustrated in Portland" <Frustrated in
wrote in message ...
I'm trying to return the mm/dd/yyyy for the next Monday on a continuing
basis. On a separate sheet I created column "A" with =TODAY() in cell A2
and filled down a series of the next six days. B2:B8 cells equal the
corresponding values from A2:A8 formated as dddd to display a continuously
rotating list of the days of the week.

I want to look up the text "Monday" from column B and return the
corresponding date value from column A. How can I use TEXT() in the
=INDEX(Sheet1!A2:B8,MATCH("Monday",Sheet1!B2:B8,0) ,2) function to make
this
work? I'm getting #N/A.

Is there an easier way?

I'm using Excel 2007

Thanks, Frustrated in Portland

Need formula to return next Monday's date
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Frustrated in Portland"
wrote in message ...

Thanks for the helpful replies.

It works!

"No longer frustrated in Portland"

"T. Valko" wrote:

Is there an easier way?

So, if today is Monday then you still want next Monday's date?

=TODAY()-WEEKDAY(NOW(),2)+8


--
Biff
Microsoft Excel MVP


"Frustrated in Portland" <Frustrated in

wrote in message
...
I'm trying to return the mm/dd/yyyy for the next Monday on a continuing
basis. On a separate sheet I created column "A" with =TODAY() in cell
A2
and filled down a series of the next six days. B2:B8 cells equal the
corresponding values from A2:A8 formated as dddd to display a
continuously
rotating list of the days of the week.

I want to look up the text "Monday" from column B and return the
corresponding date value from column A. How can I use TEXT() in the
=INDEX(Sheet1!A2:B8,MATCH("Monday",Sheet1!B2:B8,0) ,2) function to make
this
work? I'm getting #N/A.

Is there an easier way?

I'm using Excel 2007

Thanks, Frustrated in Portland