Date formats acting strange between VBA and Excel

SRRAE

New Member
Joined
Feb 8, 2013
Messages
11
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?

Code:
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
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

New Member
Joined
Feb 8, 2013
Messages
11
What other way can I change times before 9am to 9am and times after 5:30pm to 9am the next morning.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,968
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top