Matching day to date

clarkster

New Member
Joined
Jul 29, 2010
Messages
10
I would like to write a macro that will match the date to the day (see example below) So if it is June 1st for example it would be Tuesday.
<TABLE style="WIDTH: 616pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=825 border=0 x:str><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=11 width=75><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=75 height=17>Tuesday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Wednesday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Thursday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Friday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Saturday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Sunday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Monday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Tuesday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Wednesday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Thursday</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=75>Friday</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 896pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1200 border=0 x:str><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=16 width=75><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right width=75 height=17 x:num="40330">6/1/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40331">6/2/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40332">6/3/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40333">6/4/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40334">6/5/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40335">6/6/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40336">6/7/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40337">6/8/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40338">6/9/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40339">6/10/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40340">6/11/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40341">6/12/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40342">6/13/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40343">6/14/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40344">6/15/2010</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" align=right width=75 x:num="40345">6/16/2010</TD></TR></TBODY></TABLE>

This was an attempt to do it based off a script found on the internet and modified

'obtaining days of the week within each month
Dim givenDate As Date
Dim modifiedDate As Date
Dim dayNumber As Integer
Dim dayOfWeek As String
givenDate = CDate(Range("c9").Value)
modifiedDate = DateAdd("d", 2, givenDate)
dayNumber = Weekday(modifiedDate)
Select Case dayNumber
Case 1
dayOfWeek = "Monday"
Case 2
dayOfWeek = "Tuesday"
Case 3
dayOfWeek = "Wednesday"
Case 4
dayOfWeek = "Thursday"
Case 5
dayOfWeek = "Friday"
Case 6
dayOfWeek = "Saturday"
Case 7
dayOfWeek = "Sunday"
End Select
Range("C8").Value = dayOfWeek
Range("C8:Ag8").Font.Size = 10
Range("C8:Ag8").Font.Name = "Arial"
Range("C8").Select
Selection.AutoFill Destination:=Range("C8:AG8"), Type:=xlFillDefault
Range("C8:AG8").Select
'''''''''''''''''''''''''''''''''''''''''''''''''
 
Last edited:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
Excel Workbook
ABCDEFGHI
1TuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesday<< Cells A1:H1 - Custom Format - "dddd"
2
36/1/20106/2/20106/3/20106/4/20106/5/20106/6/20106/7/20106/8/2010
Sheet1
Excel 2007
Cell Formulas
RangeFormula
A1=WEEKDAY(A3)
B1=WEEKDAY(B3)
B3=A3+1
C1=WEEKDAY(C3)
C3=B3+1
D1=WEEKDAY(D3)
D3=C3+1
E1=WEEKDAY(E3)
E3=D3+1
F1=WEEKDAY(F3)
F3=E3+1
G1=WEEKDAY(G3)
G3=F3+1
H1=WEEKDAY(H3)
H3=G3+1
 

Nothingman

New Member
Joined
Jul 14, 2007
Messages
30
Could you just format the cell to only read the day of week? Just a custom format with dddd? That returns the day of week but I'm not sure if that would work for what you need.
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
hmmmmm,, what I did WASN'T a macro; Oh well
For what it's worth - I do it this way...
Jim
 

clarkster

New Member
Joined
Jul 29, 2010
Messages
10
Thanks Guys for the suggestions! I had to get a little creative. I matched the first day of the month located in c9 with the proper day by creating the following macro. I then copied and pasted it with the macro to the next 30 cells which corresponds to the correct dates. Have a great day!

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'this fills in the days of the week based off the first day of the month
[A102] = "=TEXT(WEEKDAY(c9), ""ddd"") "
Range("A102").Select
Selection.Copy
Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("C8").Select
Application.CutCopyMode = False
'copies day and autofills 31 days
Selection.AutoFill Destination:=Range("C8:AG8"), Type:=xlFillDefault
Range("C8:AG8").Select
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,133,325
Messages
5,658,169
Members
418,430
Latest member
Chlwls808

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top