CODEWITHSUNDEEP
vbscripttimestamp
Mareena W
Mareena W

Reputation: 23

Adding a specific time to an existing date stamp

My boss has asked me to write a function that can determine how much time each employee takes to complete their work in terms of hours spent per assignment. I have created two variables, one for the time work has begun and the other for the time it is completed. The beginning time stamp is to take the exact time the work is started. However, the ending time stamp is to take the day it is completed but I need to add a specific time of day.

For example, I start an assignment on 4/23/2017 4:07:00 PM. I finish the assignment on 4/24/2017. Assignments do not get checked off as completed until about 5:00 PM each day so he wants the time stamp on the finishing time to always be exactly 5:00 PM regardless of the specific time it was marked complete; only the date is of interest here.

I've successfully been able to get the exact timestamps to save but I am having inconsistencies getting my set time to stick to the determined date.

I used:

orderCompleteTimeStamp = CDate(orderCompleteDate + TimeValue("17:00"))

And it seemed to display correctly, however the actual value stored in orderCompleteTimeStamp when used in calculating the time difference is ONLY 5:00, so when it is trying to do 4/23/2017 1:55:26 PM - 4/23/2017 5:00:00 PM, the time difference shows -1028348.9. If I set orderCompleteTimeStamp back to just taking the exact time the calculation works correctly so I can only assume it is something with addition of the specific time.

Any ideas how I can get this to work?

Upvotes: 0

Views: 205

Answers (1)

user692942
user692942

Reputation: 16672

The question isn't very clear, this code works fine for me;

Dim orderStartTimeStamp: orderStartTimeStamp = CDate("23/4/2017 13:55:26")
Dim orderCompleteDate: orderCompleteDate = CDate("23/4/2017")
orderCompleteTimeStamp = CDate(orderCompleteDate + TimeValue("17:00"))

WScript.Echo orderCompleteTimeStamp
WScript.Echo DateDiff("h", orderStartTimeStamp, orderCompleteTimeStamp)

Output:

23/04/2017 17:00:00
4

Assumed you are checking the difference in hours as the question doesn't specify.

If the problem is a date that already has a time component just use DateSerial() to pull out the date components and create a new date variable.

Dim dateFromDB: dateFromDB = "23/4/2017 18:23:02"

Dim orderStartTimeStamp: orderStartTimeStamp = CDate("23/4/2017 13:55:26")
Dim orderCompleteDate: orderCompleteDate = DateSerial(Year(dateFromDB), Month(dateFromDB), Day(dateFromDB))
orderCompleteTimeStamp = CDate(orderCompleteDate + TimeValue("17:00"))
WScript.Echo orderCompleteTimeStamp
WScript.Echo DateDiff("h", orderStartTimeStamp, orderCompleteTimeStamp)

Output:

23/04/2017 17:00:00
4

Upvotes: 1

Related Questions