Date formats acting strange between VBA and Excel

[SRRAE]

Hi,
Im trying to work out the number of working hours between 2 dates and times.

I searched and found this formula
=(NETWORKDAYS(StartDT,EndDT,HolidayList)-1)*(DayEnd-DayStart)+MOD(E3,1)-MOD(D3,1)

Now this works but if the start date and is before or after working hours (before 9 and after 5) it counts counts those hours, but just for the first day. The following days it counts them correctly based on the time between the DayEnd-DayStart.

To correct this I have written a little VBA which takes the Date Time and if the time is < 9AM it sets it to 9am and if its > 5:30PM it sets the date and time to 9am the next morning.

The Excel sheet which has the date and time and the cell where I will be exporting the data and time are formated dd/mm/yyyy hh:mm:ss
I have declared variables as date in my VBA.
I split the date and time using Left and Right function.
I have used the format function to formate the date and time
DateAdd is adding the day on to the correct part of the date shows the date format is correct within VBA.
However when I then export the data back to excel it changing the date to mm/dd/yyyy hh:mm:ss which is then causing the Excel formula not to work.

Is there any reason why the date saved back to the sheet appears in the wrong format?

Private Sub RealTimeInHours()
Dim StartDate, EndDate As Date
Dim StartTime, EndTime As Date
Dim DateTime As Date

For i = 2 To 700
    DateTime = Sheets("Sheet1").Cells(i, 4)
    StartDate = Left(DateTime, 10)
    StartTime = Right(DateTime, 8)
    StartDate = Format(DateTime, "dd/mm/yyyy")
    
    If StartTime >= "00:00:00" And StartTime <= "08:59:59" Then StartTime = "09:00:00"
    If StartTime >= "17:30:00" And StartTime <= "23:59:59" Then
    StartDate = DateAdd("d", 1, StartDate)
    StartTime = "09:00:00"
    Else
    End If
    
    newstartdatetime = StartDate & " " & StartTime
    newstartdatetime = Format(newstartdatetime, "dd/mm/yyyy HH:MM:SS")
    Sheets("Sheet1").Cells(i, 6) = newstartdatetime
Next i
End Sub

 

[Firefly2012]

Left, Right and Format are String functions (that return Strings) and therefore you shouldn't be using these to extract dates or times from dates or manufacturing new dates (as they return Strings not Dates!).

"00:00:00" is a String value and so you shouldn't be using this to compare against a Date (which in Excel is a numeric quantity).

What I guess I don't follow is why you would be amending your Dates using VBA in the first place. Wouldn't it be best to amend the formulas you are using and retain the original data on the sheet?

 

[SRRAE]

What other way can I change times before 9am to 9am and times after 5:30pm to 9am the next morning.