[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How do I subtract time within visual basic for a regular set of hours.

Posted on 2000-02-08
19
Medium Priority
?
352 Views
Last Modified: 2010-05-02
Our group works an 8-5 shift monday - friday...

We would like to see how long we work on our projects..

We have a start time and end time in the following date format:

"mm/dd/yyyy hh:mm:ss PM"  (or AM)

What I need to do is subtract the start time from the end time for the amt. of time we actually worked on this project
according to our work hours.. and have that data placed in teh following format:

hhh:mm:ss.

0
Comment
Question by:tchous
  • 7
  • 5
  • 4
  • +2
19 Comments
 
LVL 10

Expert Comment

by:caraf_g
ID: 2499959
Check out the DateDiff function and the Format function.
0
 
LVL 10

Expert Comment

by:caraf_g
ID: 2499970
Example:
Option Explicit
Dim dat1 As Date

Private Sub Command1_Click()

Dim dblDiff As Double

dblDiff = DateDiff("s", dat1, Now)

'dblDiff = difference in seconds

dblDiff = dblDiff / 60 / 60 / 24

'now dblDiff = difference in days

MsgBox Format(dblDiff, "hh:mm:ss")

End Sub

Private Sub Form_Load()
dat1 = Now

End Sub
0
 
LVL 10

Expert Comment

by:caraf_g
ID: 2499978
To test this, create a form with one button on it, and paste in the code above.

Then, start the program. The current time will be put in dat1. Wait a while

Then, click the button. A message box will show you the difference in hours, minutes and seconds between when the program was started and when the button was clicked.

Hope this helps!
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:tchous
ID: 2500001
caraf_g,


I knew about the datediff function, the problem is with incorporating a standard m-f 8-5 work hours that will alter the amt. of time I worked on the project..
So that If i worked on a project on friday at 3 and stopped working on monday at 9...  Total time spent would be 3 hours..  That's where the real problem lies...
0
 
LVL 12

Expert Comment

by:roverm
ID: 2500206
Check this out:

http://www.planet-source-code.com/vb/scripts/BrowseCategoryOrSearchResults.asp?lngWId=1

look for DateFunction(s).

With these functions you can calculate everything !

grtx, RoverM
0
 
LVL 10

Accepted Solution

by:
caraf_g earned 750 total points
ID: 2500230
OK, very rough & ready. Have to stop playing with this now. work ...

Private Sub Command1_Click()

Dim datStartTime As Date
Dim datEndTime As Date
Dim lngDOWStart As Long
Dim lngDOWEnd As Long
Dim datStartW As Date
Dim datEndW As Date
Dim lngFullWeeks As Long
Dim datFirstDayEnd As Date
Dim lngFullDaysWeek1 As Long
Dim lngFullDaysWeekN As Long
Dim dblFullDay As Double
Dim dblStartEnd As Double
Dim dblEndStart As Double
Dim dblTotalTimeSpent As Double
Dim strHHMMSS As String

datStartTime = CDate("2000/02/08 11:34:45")
datEndTime = Now

lngDOWStart = CLng(Format(datStartTime, "w"))
lngDOWEnd = CLng(Format(datEndTime, "w"))

'Strip out weekend start and end times.
Select Case lngDOWStart
Case 1
    'No work on Sunday. Start Monday 8:00
    datStartTime = CDate(CDbl(Int(CDbl(datStartTime)) + 1) + CDbl(TimeSerial(8, 0, 0)))
    lngDOWStart = 2
Case 7
    'No work on Saturday. Start Monday 8:00
    datStartTime = CDate(CDbl(Int(CDbl(datStartTime)) + 2) + CDbl(TimeSerial(8, 0, 0)))
    lngDOWStart = 2
End Select
Select Case lngDOWEnd
Case 1
    'No work on Sunday. End Friday 17:00
    datEndTime = CDate(CDbl(Int(CDbl(datEndTime)) - 2) + CDbl(TimeSerial(17, 0, 0)))
    lngDOWEnd = 6
Case 7
    'No work on Saturday. End Friday 17:00
    datEndTime = CDate(CDbl(Int(CDbl(datEndTime)) - 1) + CDbl(TimeSerial(17, 0, 0)))
    lngDOWEnd = 6
End Select

'First Saturday after start
datStartW = DateAdd("d", 7 - lngDOWStart, datStartTime)
'Last saturday before end
datEndW = DateAdd("d", -(lngDOWEnd), datEndTime)

'Number of full weeks between the two dates
lngFullWeeks = DateDiff("d", datStartW, datEndW) / 7

'Number of full days until the end of the first week
lngFullDaysWeek1 = 6 - lngDOWStart

'Number of full days in the last week
lngFullDaysWeekN = lngDOWEnd - 2

'time in a full day
dblFullDay = (17 - 8) / 24 'as fraction of a day

dblStartEnd = Int(CDbl(datStartTime)) + CDbl(TimeSerial(17, 0, 0))

dblEndStart = Int(CDbl(datEndTime)) + CDbl(TimeSerial(8, 0, 0))

dblTotalTimeSpent = lngFullWeeks * 5 * dblFullDay _
                  + lngFullDaysWeek1 * dblFullDay _
                  + lngFullDaysWeekN * dblFullDay _
                  + (dblStartEnd - CDbl(datStartTime)) _
                  + (CDbl(datEndTime) - dblEndStart)

dblTotalTimeSpent = 24 * dblTotalTimeSpent

strHHMMSS = CStr(Int(dblTotalTimeSpent))

dblTotalTimeSpent = dblTotalTimeSpent - Int(dblTotalTimeSpent)

dblTotalTimeSpent = 60 * dblTotalTimeSpent

strHHMMSS = strHHMMSS & ":" & Right("00" & CStr(Int(dblTotalTimeSpent)), 2)

dblTotalTimeSpent = dblTotalTimeSpent - Int(dblTotalTimeSpent)

dblTotalTimeSpent = 60 * dblTotalTimeSpent

strHHMMSS = strHHMMSS & ":" & Right("00" & CStr(Int(dblTotalTimeSpent)), 2)

MsgBox strHHMMSS


End Sub

0
 
LVL 10

Expert Comment

by:caraf_g
ID: 2500243
Basically, you can set the start date in "yyyy/mm/dd hh:mm:ss" format (24-hour clock), it should be easy for you to adapt this to any other format if needed, but it's a universal one that always works.

It will calculate the difference between now and the start date in hours, minutes and seconds, based on a 8-5 working day, and mo-fr working week. Does not take into account public holidays <g>

Instead of Now you can obviously use another date value for the end time. This is just an example.

Hope this helps.
0
 
LVL 22

Expert Comment

by:ture
ID: 2500365
tchous,

How about this function?

Public Function TimeCounter(dtStart As Date, dtEnd As Date) As String
  Dim dtFirstDayTime As Date
  Dim dtLastDayTime As Date
  Dim intDaysBetween As Integer
  Dim dtLoopDate As Date
  Dim dtDaysBetweenTime As Date
  Dim dtTotalTime As Date
 
  If Day(dtStart) = Day(dtEnd) Then
    dtTotalTime = dtEnd - dtStart
  Else
    dtFirstDayTime = CDate("17:00") - TimeSerial(Hour(dtStart), Minute(dtStart), 0)
    dtLastDayTime = TimeSerial(Hour(dtEnd), Minute(dtEnd), 0) - CDate("09:00")
    For dtLoopDate = Int(dtStart) To Int(dtEnd)
      If Weekday(dtLoopDate, vbMonday) < 6 Then intDaysBetween = intDaysBetween + 1
    Next dtLoopDate
    intDaysBetween = intDaysBetween - 2
    dtDaysBetweenTime = intDaysBetween * (CDate("17:00") - CDate("09:00"))
    dtTotalTime = dtFirstDayTime + dtDaysBetweenTime + dtLastDayTime
  End If
 
  TimeCounter = DateDiff("h", 0, dtTotalTime) & Format(dtTotalTime, ":nn:ss")
End Function

Ture Magnusson
Karlstad, Sweden
0
 
LVL 1

Expert Comment

by:dosyl
ID: 2500735
?
0
 
LVL 22

Expert Comment

by:ture
ID: 2500762
What's your question, dosyl?
0
 
LVL 10

Expert Comment

by:caraf_g
ID: 2500816
I think dosyl is just hitching a ride.
0
 
LVL 22

Expert Comment

by:ture
ID: 2500831
Of course he is... should have understood that myself.
Where is tchous???
0
 
LVL 10

Expert Comment

by:caraf_g
ID: 2500885
Good question. Odd profile. First login 1.5 years ago, only one other question asked. I think tchous may not be the most frequent visitor to this site... Patience, my friend, patience.
0
 
LVL 1

Expert Comment

by:dosyl
ID: 2501005
Hi, guys, I put a ? because i already made code for this and my HardDrive where is my code is scrap, and your code seem better that mine because i coded all and didn't use Vb functions.
Hope you're not angrys.
0
 
LVL 22

Expert Comment

by:ture
ID: 2501024
dosyl,

Not angry at all. Sharing and helping each other is what this place is all about. I'm glad if my code can be useful for you.

/Ture
0
 

Author Comment

by:tchous
ID: 2501812
thanks ture and caraf_g...  I don't really hav ethe time to test what each of you did, so I will give each of you the points for the answer..
0
 

Author Comment

by:tchous
ID: 2501824
If i can figure out a way that is..
0
 

Author Comment

by:tchous
ID: 2501836
I rated this as good, I do wish though that I could give ture 250 as well since I really havent' tested each situation for my application..
0
 
LVL 22

Expert Comment

by:ture
ID: 2503154
tchous,

If you wish to give me 250 points as well, this is how to do it:

1. Post a new question in this topic area with the subject 'Points for Ture'.

2. In the text of the question, type
'For help with http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=10291847 '

3. Wait for my answer and grade it.

/Ture
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question