Search code examples
excelvbadateformatdayofweek

VBA Excel Getting the Day from a Date

I have the following VBA Code that generates a list of dates, what I want is as I print out every date, to print the date in the format of day i.e. 01/10/2002 would be Monday:

Sub loopDates()
    Dim Stdt    As Date
    Dim Edt     As Date
    Dim n       As Date
    Dim c       As Long
    Stdt = Range("A1")
    Edt = Range("A2")
    For n = Stdt To Edt
            c = c + 1
            Range("C" & c) = n
            Range("B" & c).Formula = "=TEXT(C1,""dddd"")"
    Next n
End Sub

I want to change the line:

Range("B" & c).Formula = "=TEXT(C1,""dddd"")"

So that C1 in this example is change every time the loop is iterated, so it would change with the value c, for example it would look like =TEXT(C " & c &, "dddd") but I cannot get the formatting right,

Can anyone propose a solution to this problem?

Solution

  • I've managed to find from here that you can just set the format of the cell. I changed the line mentioned in the original post to:

    Range("B" & c) = Format(n, "dddd")

    This takes the date from column and index of C and prints the day in the right format to the column and index of B

    Thanks for answering