Remember Me?

#1
January 2nd 07, 07:25 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 18
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.
Thanks

#2
January 2nd 07, 07:58 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 2,118
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.
Thanks

#3
January 2nd 07, 08:17 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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.
Thanks

#4
January 2nd 07, 08:28 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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.
Thanks

#5
January 2nd 07, 08:33 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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.
Thanks

#6
January 2nd 07, 08:51 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 3,718
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.
Thanks

#7
January 2nd 07, 08:52 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 2,118
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.
Thanks

#8
January 2nd 07, 10:11 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 18
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.
Thanks

#9
January 4th 07, 01:47 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 1
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.
Thanks

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

All times are GMT +1. The time now is 06:31 AM.