Return dates based on month and day of week
 

I need to return the correct date based on the day of week and month of year.
e.g.
in A1 I type January
in A2 I type Monday

in A4 I want the result 1 Jan 2007
in A5 I want 8 Jan 2007
etc.
So that if I change either the month or day in A1 or A2, the results will
change automatically.
Please can anyone help? This is driving me mad!!
Thanks

Return dates based on month and day of week
 

Try something like this:

With
A1: (a month name)
A2: (a day name)

A4:
=+("01-"&A1)+MATCH(1,--(TEXT(+("01-"&A1)+{0,1,2,3,4,5,6},"ddd")=LEFT(A2,3)),0)-1

A5: =A4+7
Copy that formula down as far as needed

Example:
A1: February
A2: Tuesday

A4: returns February 06, 2007

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"lesg46" wrote:

I need to return the correct date based on the day of week and month of year.
e.g.
in A1 I type January
in A2 I type Monday

in A4 I want the result 1 Jan 2007
in A5 I want 8 Jan 2007
etc.
So that if I change either the month or day in A1 or A2, the results will
change automatically.
Please can anyone help? This is driving me mad!!
Thanks

Return dates based on month and day of week
 

Very nice! That goes in my stash!

Here's what I came up with:

=DATEVALUE(A$1&1)+ROWS($1:1)*7-WEEKDAY(DATEVALUE(A$1&1)+7-MATCH(LEFT(A$2,3),{"Sun","Mon","Tue","Wed","Thu"," Fri","Sat"},0))

Biff

"Ron Coderre" wrote in message
...
Try something like this:

With
A1: (a month name)
A2: (a day name)

A4:
=+("01-"&A1)+MATCH(1,--(TEXT(+("01-"&A1)+{0,1,2,3,4,5,6},"ddd")=LEFT(A2,3)),0)-1

A5: =A4+7
Copy that formula down as far as needed

Example:
A1: February
A2: Tuesday

A4: returns February 06, 2007

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"lesg46" wrote:

I need to return the correct date based on the day of week and month of
year.
e.g.
in A1 I type January
in A2 I type Monday

in A4 I want the result 1 Jan 2007
in A5 I want 8 Jan 2007
etc.
So that if I change either the month or day in A1 or A2, the results will
change automatically.
Please can anyone help? This is driving me mad!!
Thanks

Return dates based on month and day of week
 

With some more tweaking, DATEVALUE is not needed:

=(A$1&1)+ROWS($1:1)*7-WEEKDAY(A$1&1+7-MATCH(LEFT(A$2,3),{"Sun","Mon","Tue","Wed","Thu"," Fri","Sat"},0))

Biff

"T. Valko" wrote in message
...
Very nice! That goes in my stash!

Here's what I came up with:

=DATEVALUE(A$1&1)+ROWS($1:1)*7-WEEKDAY(DATEVALUE(A$1&1)+7-MATCH(LEFT(A$2,3),{"Sun","Mon","Tue","Wed","Thu"," Fri","Sat"},0))

Biff

"Ron Coderre" wrote in message
...
Try something like this:

With
A1: (a month name)
A2: (a day name)

A4:
=+("01-"&A1)+MATCH(1,--(TEXT(+("01-"&A1)+{0,1,2,3,4,5,6},"ddd")=LEFT(A2,3)),0)-1

A5: =A4+7
Copy that formula down as far as needed

Example:
A1: February
A2: Tuesday

A4: returns February 06, 2007

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"lesg46" wrote:

I need to return the correct date based on the day of week and month of
year.
e.g.
in A1 I type January
in A2 I type Monday

in A4 I want the result 1 Jan 2007
in A5 I want 8 Jan 2007
etc.
So that if I change either the month or day in A1 or A2, the results
will
change automatically.
Please can anyone help? This is driving me mad!!
Thanks

Return dates based on month and day of week
 

Not sure what the "+" are for but it works without them:

=("01-"&A1)+MATCH(1,--(TEXT(("01-"&A1)+{0,1,2,3,4,5,6},"ddd")=LEFT(A2,3)),0)-1

Biff

"Ron Coderre" wrote in message
...
Try something like this:

With
A1: (a month name)
A2: (a day name)

A4:
=+("01-"&A1)+MATCH(1,--(TEXT(+("01-"&A1)+{0,1,2,3,4,5,6},"ddd")=LEFT(A2,3)),0)-1

A5: =A4+7
Copy that formula down as far as needed

Example:
A1: February
A2: Tuesday

A4: returns February 06, 2007

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"lesg46" wrote:

I need to return the correct date based on the day of week and month of
year.
e.g.
in A1 I type January
in A2 I type Monday

in A4 I want the result 1 Jan 2007
in A5 I want 8 Jan 2007
etc.
So that if I change either the month or day in A1 or A2, the results will
change automatically.
Please can anyone help? This is driving me mad!!
Thanks

Return dates based on month and day of week
 

A4=TEXT($A$1&
VLOOKUP($A$2,{"Monday",1;"Tuesday",2;"Wednesday",3 ;"Thurday",4;"Friday",5;"Saturday",6;"Sunday",7},2 ,0)+(7*(ROWS($1:1)-1)),"d mmm yyyy")

drag from A4 down as far as needed


"lesg46" wrote:

I need to return the correct date based on the day of week and month of year.
e.g.
in A1 I type January
in A2 I type Monday

in A4 I want the result 1 Jan 2007
in A5 I want 8 Jan 2007
etc.
So that if I change either the month or day in A1 or A2, the results will
change automatically.
Please can anyone help? This is driving me mad!!
Thanks

Return dates based on month and day of week
 

Thanks for the compliment, Biff.......much appreciated.

(oh...and the extra plus signs were just to emphatically ensure date
coersion...since I was a bit short on available testing time)
***********
Regards,
Ron

XL2002, WinXP


"T. Valko" wrote:

Very nice! That goes in my stash!

Here's what I came up with:

=DATEVALUE(A$1&1)+ROWS($1:1)*7-WEEKDAY(DATEVALUE(A$1&1)+7-MATCH(LEFT(A$2,3),{"Sun","Mon","Tue","Wed","Thu"," Fri","Sat"},0))

Biff

"Ron Coderre" wrote in message
...
Try something like this:

With
A1: (a month name)
A2: (a day name)

A4:
=+("01-"&A1)+MATCH(1,--(TEXT(+("01-"&A1)+{0,1,2,3,4,5,6},"ddd")=LEFT(A2,3)),0)-1

A5: =A4+7
Copy that formula down as far as needed

Example:
A1: February
A2: Tuesday

A4: returns February 06, 2007

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"lesg46" wrote:

I need to return the correct date based on the day of week and month of
year.
e.g.
in A1 I type January
in A2 I type Monday

in A4 I want the result 1 Jan 2007
in A5 I want 8 Jan 2007
etc.
So that if I change either the month or day in A1 or A2, the results will
change automatically.
Please can anyone help? This is driving me mad!!
Thanks

Return dates based on month and day of week
 

Biff & Ron,
Thanks very much indeed guys. All suggestions work perfectly!
I love Excel to pieces, and can only aspire to knowing as much about it as
you do....
Cheers, and Happy New Year
Lesley

"T. Valko" wrote:

Not sure what the "+" are for but it works without them:

=("01-"&A1)+MATCH(1,--(TEXT(("01-"&A1)+{0,1,2,3,4,5,6},"ddd")=LEFT(A2,3)),0)-1

Biff

"Ron Coderre" wrote in message
...
Try something like this:

With
A1: (a month name)
A2: (a day name)

A4:
=+("01-"&A1)+MATCH(1,--(TEXT(+("01-"&A1)+{0,1,2,3,4,5,6},"ddd")=LEFT(A2,3)),0)-1

A5: =A4+7
Copy that formula down as far as needed

Example:
A1: February
A2: Tuesday

A4: returns February 06, 2007

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"lesg46" wrote:

I need to return the correct date based on the day of week and month of
year.
e.g.
in A1 I type January
in A2 I type Monday

in A4 I want the result 1 Jan 2007
in A5 I want 8 Jan 2007
etc.
So that if I change either the month or day in A1 or A2, the results will
change automatically.
Please can anyone help? This is driving me mad!!
Thanks

Return dates based on month and day of week
 

Do you want all Mondays or a specific Monday for the month of January
--
Many Thanks
Bill


"lesg46" wrote:

I need to return the correct date based on the day of week and month of year.
e.g.
in A1 I type January
in A2 I type Monday

in A4 I want the result 1 Jan 2007
in A5 I want 8 Jan 2007
etc.
So that if I change either the month or day in A1 or A2, the results will
change automatically.
Please can anyone help? This is driving me mad!!
Thanks