CODEWITHSUNDEEP
vbadatevariablesformat
idontknowwhatiamdoing
idontknowwhatiamdoing

Reputation: 11

VBA - setting a date format to a variable

Novice VBA user here. I think this might be a very easy question to answer, but I cant find the solution on here on any other site so apologies if that's the case.

I am writing a very short piece of code that inserts the latest business day into a column where its not already present and moves onto the next column and runs the same process where it is present. The code is below. Note I have simplified this by but removing the else condition. I want to set the date format for the lastbusinessday variable to be "dd/mm/yyyy" - at the moment its including hh/mm/ss which is causing the if function to not work properly.

How can i declare the conditions for this variable?

Sub Dateextend4()
    Dim lastbusinessday As Date

    If Weekday(Now()) = 2 Then
        lastbusinessday = (Now() - 3)
    Else
        lastbusinessday = (Now() - 1)
    End If

    Sheets("pb CDS").Select
    Range("b13").Select
    Selection.End(xlDown).Select

    If ActiveCell.Offset(0, 0).Value < lastbusinessday Then
        ActiveCell.Offset(1, 0) = format(lastbusinessday, "mm / dd / yyyy")
    Else
    End If
End Sub

Thanks

Upvotes: 1

Views: 13573

Answers (1)

ste-fu
ste-fu

Reputation: 7434

Try this

If Weekday(Date()) = 2 Then
    lastbusinessday = (Date() - 3)
Else
    lastbusinessday = (Date() - 1)
End If

Format(lastbusinessday, "dd/MM/yyyy")

Upvotes: 1

Related Questions