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

 

[chrismeinders]

Here's a shorter version

Public Function fwday(Optional givenDate As Variant) As Date
    Dim dtTemp As Date, wdTemp As Integer
    If IsMissing(givenDate) Then givenDate = Date
    dtTemp = DateSerial(Year(givenDate), Month(givenDate), 1)
    wdTemp = Weekday(dtTemp)
    If wdTemp = 1 Then dtTemp = DateSerial(Year(givenDate), Month(givenDate), 2)
    If wdTemp = 7 Then dtTemp = DateSerial(Year(givenDate), Month(givenDate), 3)
    fwday = dtTemp
End Function

 

[Eric Penfold]

Here's a shorter version

Public Function fwday(Optional givenDate As Variant) As Date
    Dim dtTemp As Date, wdTemp As Integer
    If IsMissing(givenDate) Then givenDate = Date
    dtTemp = DateSerial(Year(givenDate), Month(givenDate), 1)
    wdTemp = Weekday(dtTemp)
    If wdTemp = 1 Then dtTemp = DateSerial(Year(givenDate), Month(givenDate), 2)
    If wdTemp = 7 Then dtTemp = DateSerial(Year(givenDate), Month(givenDate), 3)
    fwday = dtTemp
End Function

Thanks much neater I will use this.