Home 
Search 
Today's Posts 
#1




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




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




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)*7WEEKDAY(DATEVALUE(A$1&1)+7MATCH(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




Return dates based on month and day of week
With some more tweaking, DATEVALUE is not needed:
=(A$1&1)+ROWS($1:1)*7WEEKDAY(A$1&1+7MATCH(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)*7WEEKDAY(DATEVALUE(A$1&1)+7MATCH(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




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




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




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)*7WEEKDAY(DATEVALUE(A$1&1)+7MATCH(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




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




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 
Display Modes  

