How to use the FORMAT Function with Dates in VBA. Working with dates in Excel is useful, important and confusing. Dates are formatted in Excel using month/day/year format. If you use another formatting like day/month/year, transfer of data from worksheet to worksheet, workbook to workbook or from user-form to worksheet can be confusing. The date formatting function can bring some sanity into the process also during calculations using functions like datediff. Watch the video below:

Watch the video on YouTube.

Here’s the complete VBA code:

Private Sub CommandButton1_Click()
Dim date1 As Date, date2 As Date
Dim erow As Long

date1 = Format(TextBox1.Text, “dd-mm-yyyy”)
date2 = Format(TextBox2.Text, “dd-mm-yyyy”)
If date1 > date2 Then
TextBox1.SetFocus
MsgBox “The start date cannot be greater than the end date!”
Exit Sub
End If

TextBox3.Text = DateDiff(“d”, date1, date2)

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = date1
Cells(erow, 1) = Format(Cells(erow, 1), “Long Date”)
Cells(erow, 2) = date2
Cells(erow, 2) = Format(Cells(erow, 2), “Long Date”)
Cells(erow, 3) = TextBox3.Value

End Sub

Further reading:

How to use the FORMAT Function with Dates (VBA)

Datediff function in user-form