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

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.

tchousAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

caraf_gCommented:
Check out the DateDiff function and the Format function.
0
caraf_gCommented:
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
caraf_gCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

tchousAuthor Commented:
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
rovermCommented:
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
caraf_gCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
caraf_gCommented:
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
tureCommented:
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
dosylCommented:
?
0
tureCommented:
What's your question, dosyl?
0
caraf_gCommented:
I think dosyl is just hitching a ride.
0
tureCommented:
Of course he is... should have understood that myself.
Where is tchous???
0
caraf_gCommented:
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
dosylCommented:
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
tureCommented:
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
tchousAuthor Commented:
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
tchousAuthor Commented:
If i can figure out a way that is..
0
tchousAuthor Commented:
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
tureCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.