date formatting within vba

[shina67]

Hi All,

I have the following piece of code within a workbook.

Private Sub CheckForMissingReturnToWorkDates()
    Dim ws As Worksheet
    Dim wsHolidays As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim employeeName As String
    Dim dateOfAbsence As String
    Dim reason As String
    Dim inputDate As String
    Dim dateOfAbsenceDate As Date
    Dim returnDate As String
    Dim holidaysRange As Range
    Dim userResponse As VbMsgBoxResult

    On Error GoTo ErrorHandler

    Set ws = ThisWorkbook.Sheets("Absences")
    Set wsHolidays = ThisWorkbook.Sheets("Bank Holidays")
    lastRow = ws.Cells(ws.Rows.count, "A").End(xlUp).Row
    Set holidaysRange = wsHolidays.Range("A2:A" & wsHolidays.Cells(wsHolidays.Rows.count, "A").End(xlUp).Row)

    For i = 2 To lastRow
        If ws.Cells(i, 5).Value <> "Late" And IsEmpty(ws.Cells(i, 3).Value) Then
            employeeName = ws.Cells(i, 1).Value
            dateOfAbsence = ws.Cells(i, 2).Value
            reason = ws.Cells(i, 4).Value

            dateOfAbsenceDate = CDate(dateOfAbsence)

            userResponse = MsgBox("Has " & employeeName & " returned to work from their absence on " & _
                                  dateOfAbsence & " for " & reason & "?", vbYesNo + vbQuestion, "Return to Work")

            If userResponse = vbYes Then
                ' Prompt the user for the return to work date
                Do
                    inputDate = Application.InputBox("Please enter the return to work date (dd/mm/yyyy):", "Return to Work Date")
                    
                    If inputDate = "" Then Exit Do
                    
                    If IsDate(inputDate) Then
                        ' Ensure the input is formatted correctly with slashes
                        returnDate = Replace(inputDate, "-", "/")

                        ws.Cells(i, 3).Value = returnDate  ' Store as a string

                        ws.Cells(i, 7).Value = Application.WorksheetFunction.NetworkDays_Intl(dateOfAbsenceDate, CDate(returnDate), 1, holidaysRange)
                        
                        Exit Do
                    Else
                        MsgBox "Invalid date format. Please enter the date in the format dd/mm/yyyy.", vbExclamation
                    End If
                Loop
            End If
        End If
    Next i

    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical
End Sub


Private Function ForceUKDateFormat(dateStr As String) As Date
    Dim dayPart As Integer
    Dim monthPart As Integer
    Dim yearPart As Integer

    dayPart = CInt(Split(dateStr, "/")(0))
    monthPart = CInt(Split(dateStr, "/")(1))
    yearPart = CInt(Split(dateStr, "/")(2))

    ForceUKDateFormat = DateSerial(yearPart, monthPart, dayPart)
End Function

The problem I am having is that if I enter for example 2-9-24 it inputs it into my sheet as 09/02/2024 instead of 02/09/2024. This seems to be the case if the day is less than 13 in all entries. I have tried multiple different ways to try and get this to work but am now at a point where I am unsure what I have tried/haven't tried and have probably tried the same thing several times.

Can one of you kind folk please help before the last bit of remaining hair I have left gets ripped out.

Thanks in advance.

 

[FormR]

Hi, try changing this line ws.Cells(i, 3).Value = returnDate ' Store as a string to ws.Cells(i, 3).Value = CDate(returnDate) ' Store as a date

 

[kvsrinivasamurthy]

After this line

ForceUKDateFormat = DateSerial(yearPart, monthPart, dayPart)

add

ActiveCell.NumberFormat = "dd/mm/yyyy"

 

[shina67]

Hi, try changing this line ws.Cells(i, 3).Value = returnDate ' Store as a string to ws.Cells(i, 3).Value = CDate(returnDate) ' Store as a date

Thank you FormR that worked a treat.