Search code examples
excelvbadateformatcell

Write date in "dd/mm/yyyy" format

I write the date entered in a form to an Excel sheet. The format of the date in the form's input field is "dd/mm/yyyy".

When I run the code, the day, month and year are correctly identified. However, it will write the date to a sheet in the format "mm/dd/yyyy" and mix up the day and month.

E.g.:
date entered: 10/07/2023 (July 10th)
code writes: 7/10/2023 (Oct 7th)

How can I write the date in the format "dd/mm/yyyy"?

Private Sub OKButton_Click()
    ...
    'testing date
    Dim orderDate As Date
    Dim dayDate As Integer
    Dim monthDate As Integer
    Dim yearDate As Integer
    orderDate = releaseDate.Value
    dayDate = day(releaseDate.Value)
    monthDate = Month(releaseDate.Value)
    yearDate = Year(releaseDate.Value)
    
    Cells(lastRow, 7).Value = Format(releaseDate.Value, "dd / mm / yyyy")
    ...
End Sub

Form Input
Form input

Date in code
Date in code

Date in sheet
Date in sheet

Solution

  • Based on inputs provided, I added below code which solved the issue. Thanks everyone for your help!

    Dim orderDate As Date
Dim integerDate As Double
orderDate = releaseDate.Value
integerDate = Int(CDbl(orderDate))
Cells(lastRow, 7).Value = integerDate