Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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


  #2   Report Post  
Old 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
Default 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

  #3   Report Post  
Old 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
Default 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



  #4   Report Post  
Old 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
Default 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





  #5   Report Post  
Old 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
Default 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





  #6   Report Post  
Old 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
Default 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

  #7   Report Post  
Old 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
Default 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




  #8   Report Post  
Old 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
Default 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




  #9   Report Post  
Old 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
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



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

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017