Could any body help me to find 1st working day in the month.

[Eric Penfold]

This code finds first day in month but i need first working day i.e. 02/04 at the moment it returns 01/04.

Function fwday(Optional givenDate As Variant) As Date

    Dim d As Date

    If IsMissing(givenDate) Then givenDate = Date

    d = DateSerial(Year(givenDate), Month(givenDate), 1)
    d = d + ((9 - (d Mod 7)) Mod 7)
    fwday = d
    
End Function

 

[Eric Penfold]

The code below seems to work. Managed to work it out myself.

Option Explicit

Sub FillDates()

Dim wb As Workbook
Dim ws As Worksheet
Dim FCell As Range
Dim LCell As Range
Dim LRow   As Long

Set wb = Workbooks("DailyMail.xlsx")
Set ws = wb.Worksheets("Daily Mail Update")
Set FCell = ws.Range("A2")
Set LCell = ws.Range("A2" & LRow)
LRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

If Not Date = Application.WorkDay(DateSerial(Year(Date), Month(Date), 0), 1) Or _
    Date = Application.WorkDay(DateSerial(Year(Date), Month(Date), 0), 2) Then
    
FCell.Clear

FCell = Evaluate("Workday(EOMonth(Now(),-1),1)")
   
With FCell
    .HorizontalAlignment = xlCenter
    .NumberFormat = ("dd/mm/yy")
End With

End If

End Sub