CODEWITHSUNDEEP
stringexceldatevba
Derrik
Derrik

Reputation: 1

Converting all dates in a year into multiple strings using Excel VBA

I have to write a vba code that convert all dates in a year into days in a week eg. Monday, Tuesday, Wednesday....Sunday and the value of all days in a week eg.Monday must be in a string format. (See image inserted)

http://i.imgur.com/V9LyxpG.png

I tried using the .NumberFormat function but it does not work since the format is still under the date format although the values displayed are correct (please look at the formula bar of F10). Below is the code I have written:

Sub convertdate()

Range("A7:A371").Copy 
'copy all the dates throughout the year'

Range("F7:F371").PasteSpecial xlPasteFormulasAndNumberFormats 
'paste it into F column'

Worksheets("Sheet1").Range("F7:F371").NumberFormat = "dddd" 
'convert the dates into days'

Sum1 = Application.SumIf(Range("F7:F371"), "Monday", Range("B7:B371")) 
'example of calculating the total rainfall of all Mondays throughout the year'

Worksheets("Sheet1").Range("G22").FormulaArray = Sum1

End Sub

The formula bar from cell F7:F371 should display the string value "Monday","Tuesday" etc depending on the dates rather than the date itself.The reason of converting it into a string is so that it could be use in a SUMIF function later on to calculate the total rainfall of all Mondays,Tuesday etc.

Appreciate if anyone could help. Thank you.

Upvotes: 0

Views: 119

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

A formula can do this.
In cell F7 enter =TEXT(A7,"dddd") and drag down.
This will format the date to show the full day name as a string.
https://support.office.com/en-us/article/TEXT-function-20d5ac4d-7b94-49fd-bb38-93d29371225c

Upvotes: 3

TheGuyThatDoesn'tKnowMuch
TheGuyThatDoesn'tKnowMuch

Reputation: 1250

Try something like this. It will loop through all the dates in Column A (starting in row 7) and put the associated Weekday name in Column F:

Sub test()

Dim i As Long
Dim lRow As Long

    With ActiveSheet

        lRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        For i = 7 To lRow

            .Cells(i, 6).Value = WeekdayName(Weekday(.Cells(i, 1).Value, 1), False, 1)

        Next i

    End With

End Sub

Upvotes: 2

Related Questions