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
|