How to extract the "DDD" format from a date?

[Chilts]

Hi

I have never used VBA before and need help with the following.

I need to convert a date to the shortened abbreviation of the day of the week that the date relates to. i.e. 26/06/2017 = Mon.

in excel I can achieve this by using =text(A1,"ddd"), when i have incorporated this into a VBA statement, it just returns #name , please can someone point me in the right direction.

Thanks

 

[JackDanIce]

Welcome to the board. Try:

MsgBox Format(Date, "DDD")

 

[Chilts]

Thanks for that, I guess i need to be very specific about what I need.

I have an excel sheet, that has the date split out into 3 columns i.e. cell a1 has the day number (1 - 31) cell b1 has the month number ( 1 -12) and cell c1 has the year, so for example we have (A1)26 (B1)06 (C1)2017 - I have managed to write the code that runs from the top of the list to the bottom and converts the 3 cells on 1 row into a normal date, by using =Date(c1,b1,a1) the result (26/05/2017) appears in D1, which is great, I now need to write the code that will convert the date in cell D1 into the name of the day, in this case "Mon". Although I have only referenced 1 row, in reality i have hundreds of rows with dates that I need to convert into the abbreviated name of the week. I am happy with the code that makes it go from the top to bottom of the list, I just dont know the code that does what the excel function =text(A1,"ddd") would do.
cheers

 

[JackDanIce]

Try this on a copy of your worksheet, it assumes data starts in A1 as you have described:

Sub Macro1()


    Dim x       As Long
    Dim arr()   As Variant
    
    Application.ScreenUpdating = False
    
    x = Cells(Rows.Count, 1).End(xlUp).row
    arr = Cells(1, 1).Resize(x, 4).Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        arr(x, 4) = Format(DateSerial(arr(x, 3), arr(x, 2), arr(x, 1)), "DDD")
    Next x
    
    Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    
    Application.ScreenUpdating = True
    
    Erase arr
    
End Sub

 

[Chilts]

Brilliant JackDanIce - that works a treat

 

[JackDanIce]

You're welcome