Add Day and Month in "DDD" & "MMM" format

[Nathan95]

Hey All,

I have the bellow code which lists all the dates within a date range that is filled in through a user form, as well as lists the team name and employee besides it.

What Im looking to do however is in column "F" I want it to list the day depending on the date eg. "mon" and in column G i want it to list the Month depending on the date eg. "Jul"

Any help is greatly appreciated

Private Sub cbInputleave_Click()
Dim i As Long, k As Long, arr()
Dim FirstDate As Date, LastDate As Date
Dim rBlanks As Range
Dim ssheet As Worksheet
Dim ctl As Control
Dim Index As Long

Set ssheet = ThisWorkbook.Sheets("Mark Leave")
FirstDate = tbstartdate.Value
LastDate = tbenddate.Value

ReDim arr(1 To LastDate - FirstDate + 1, 1 To 1)
For i = FirstDate To LastDate
If Weekday(i, 2) < 6 And WorksheetFunction.CountIf(Sheets("Settings").Range("D2:D18"), i) = 0 Then
k = k + 1
arr(k, 1) = i
End If
Next

With ssheet.Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(arr), 1)
.Value = arr

.NumberFormat = "dd-mmm-yy"
.Offset(, 1).Value = cbLeaveType.Value
.Offset(, -1).Value = CbName.Value
.Offset(, -2).Value = ssheet.Evaluate(.Offset(, -1).Address & "&" & .Address)
.Offset(, 2).Value = cbTeam.Value
End With
MsgBox "Leave Entered"
Unload Me
End Sub

 

[bebo021999]

add this

With ssheet.Range("F" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(arr), 1)
   .Value = arr
   .NumberFormat = "ddd"
End With
With ssheet.Range("G" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(arr), 1)
   .Value = arr
   .NumberFormat = "mmm"
End With