CODEWITHSUNDEEP
vbaexcel
Pratyush Kumar
Pratyush Kumar

Reputation: 1

How to Display returned mystring value as date in VBA

I have created a function to get absent days based on absence entries in my attendance sheet.

My code is: 

Dim cell As Range
Dim myString As String

'Loop through all the cells in the range
For Each cell In myRange
    'Check for value
    If cell = "A" Then
        myString = myString & Cells(myRow, cell.Column) & ", "
    End If
Next cell

'Return string
If Len(myString) > 0 Then
    AbsentDays = Left(myString, Len(myString) - 2)
End If

End Function

I want to display the returned values displayed as "dd mmm".

Upvotes: 0

Views: 74

Answers (2)

YowE3K
YowE3K

Reputation: 23984

Just because I like to keep changes to a minimum, I think you can simply change your line saying:

myString = myString & Cells(myRow, cell.Column) & ", "

to be:

myString = myString & Format(CDate(Cells(myRow, cell.Column)), "dd mmm") & ", "

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33692

I am assuming that AbsentDays is a String, and you are getting 6/7/2017, 6/9/2017, 6/15/2017, 6/16/2017 as a String (and not a Date).

Try the String conversion code below:

Dim AbsentDays  As String
Dim DatesArr As Variant
Dim i As Long

' for my tests
AbsentDays = "6/7/2017, 6/9/2017, 6/15/2017, 6/16/2017"

' use the split to get the values in an array
DatesArr = Split(AbsentDays, ",")
Dim myDateformat As Variant

' loop through array
For i = 0 To UBound(DatesArr)
    If i = 0 Then ' first array element
        myDateformat = Format(CDate(DatesArr(i)), "dd mmm")
    Else ' 2nd array element and above
        myDateformat = myDateformat & ", " & Format(CDate(DatesArr(i)), "dd mmm")
    End If
Next i

MsgBox myDateformat

Upvotes: 1

Related Questions