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:

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
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,471
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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,298
Messages
5,510,471
Members
408,791
Latest member
bwirth

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top