If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Function to add up total time taken?
Ok I'm posting this here as you guys have been so helpful in the past.
I've been asked to do a query to work out the total working hours taken to do a job. I've done it as a function because its the only way I could figure out. Time taken should be from 8 til 8 five days a week - so far it SEEMS to work but it's a bit buggy, and it only does 8 til 8 but includes weekends - I want it to count only weekdays. So if the job starts at 7pm on a friday and finishes at 10am on a monday that's only three working hours. This is what I've got so far, if anyone can give me any tips to get the weekends working, or on what I've done wrong here I'd appreciate it! (Oh, time is stored in a text field which I intend to strip out later, because I want the time taken in HOURS, even if it took 115 hours and 30 minutes - not the equivalent in days/hours/minutes or whatever) Thanks in advance! -- Tails 'variable declarations Dim timeTakenMins As Integer Dim timeTakenHours As Integer Dim timeTakenRemMins As Integer Dim timeOne As Integer Dim timeTwo As Integer Dim timeWholeDays As Integer Dim closingTime As Date Dim openingTime As Date Dim totalTime As String Dim timeConvHours As String Dim daysTaken As Integer 'declare closing and opening time - time stops being counted outside these hours closingTime = #8:00:00 PM# openingTime = #8:00:00 AM# 'calculate days taken daysTaken = DateDiff("d", dateEntered, dateCompleted) 'it is easy to work out time taken if only one day If daysTaken = 0 Then timeTakenMins = DateDiff("n", timeEntered, timeCompleted) timeTakenHours = timeTakenMins \ 60 'returns a whole number, no rounding timeTakenRemMins = timeTakenMins Mod 60 timeConvHours = "00" 'keep all final totals in the same format totalTime = timeConvHours & ":" & timeTakenRemMins 'different method for one day ElseIf daysTaken = 1 Then timeOne = DateDiff("n", timeEntered, closingTime) 'time taken on day one timeTwo = DateDiff("n", openingTime, timeCompleted) 'time taken on day two timeTakenMins = timeOne + timeTwo 'total them up timeTakenHours = timeTakenMins \ 60 timeTakenRemMins = timeTakenMins Mod 60 If timeTakenHours 10 Then timeConvHours = "0" + timeTakenHours 'keep the final total in the same format totalTime = timeTakenHours & ":" & timeTakenRemMins 'keep the final total in the same format 'different method for more than one day ElseIf daysTaken 1 Then timeOne = DateDiff("n", timeEntered, closingTime) 'time taken on day one timeTwo = DateDiff("n", openingTime, timeCompleted) 'time taken on final day timeWholeDays = (daysTaken - 1) * 720 'twelve hours (720 mins) for each day in between timeTakenMins = timeOne + timeTwo + timeWholeDays 'total them up timeTakenHours = timeTakenMins \ 60 timeTakenRemMins = timeTakenMins Mod 60 timeConvHours = timeTakenHours totalTime = timeConvHours & ":" & timeTakenRemMins 'keep it all in the same format Else 'unable to do anything as dates are wrong 'may include message box to state name of record incorrect? End If main = totalTime 'return total time |
#2
|
|||
|
|||
Function to add up total time taken?
Hi Tails,
I'd use one of the ready-made working days functions from http://www.mvps.org/access/datetime/date0006.htm . They do the hard work (especially if you need to allow for public holidays), and the rest of the code can be pretty simple, e.g. this (which hasn't been properly tested and doesn't guard against incorrect inputs): Function WkgHrs(Start As Date, Finish As Date) As Double 'Working hours between two times Dim dtOpeningTime As Date 'start and end of working day Dim dtClosingTime As Date Dim dblHrs As Double 'accumulator dtOpeningTime = #8:00:00 AM# dtClosingTime = #8:00:00 PM# If Int(Start) = Int(Finish) Then 'Start and finish on same day dblHrs = (Finish - Start) * 24 Else 'If we're here, start and finish on different days. 'Hours on first day dblHrs = (dtClosingTime - (Start - Int(Start))) * 24 'Hours on last day dblHrs = dblHrs + (Finish - Int(Finish) - dtOpeningTime) * 24 'Hours on intervening days dblHrs = dblHrs + (WorkingDays(Start, Finish)) _ * (dtClosingTime - dtOpeningTime) * 24 End If WkgHrs = dblHrs End Function On Sun, 08 Aug 2004 22:06:28 GMT, "Tails" wrote: Ok I'm posting this here as you guys have been so helpful in the past. I've been asked to do a query to work out the total working hours taken to do a job. I've done it as a function because its the only way I could figure out. Time taken should be from 8 til 8 five days a week - so far it SEEMS to work but it's a bit buggy, and it only does 8 til 8 but includes weekends - I want it to count only weekdays. So if the job starts at 7pm on a friday and finishes at 10am on a monday that's only three working hours. This is what I've got so far, if anyone can give me any tips to get the weekends working, or on what I've done wrong here I'd appreciate it! (Oh, time is stored in a text field which I intend to strip out later, because I want the time taken in HOURS, even if it took 115 hours and 30 minutes - not the equivalent in days/hours/minutes or whatever) Thanks in advance! -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Total time duration formula help; | Paul | Worksheet Functions | 2 | June 22nd, 2004 09:30 PM |
TIME and IF function | Mediaexcel | Worksheet Functions | 0 | April 23rd, 2004 08:13 PM |
Writing a custom function | sue | Worksheet Functions | 2 | April 5th, 2004 06:02 PM |
Sum of total time in arange of cells | Worksheet Functions | 6 | February 11th, 2004 04:49 PM | |
IF function using time | Lorrie | Worksheet Functions | 2 | January 7th, 2004 12:35 AM |