| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 | |
|
|
Hybrid Mode
