473,573 Members | 4,141 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Calculating hours and minutes

3 New Member
I really like the site so far and this is my first post. I have looked through some of the archives with no luck. I have also read the posting guidelines and will do my best to be clear and accurate with my problem. I am working with Access 2k / Win 2k. I have some experience and must have this figured out before Aug1.

What i am tasked with is to modify an existing process that calculates the hours and minutes worked, which is entered on a form using controls bound to fields in a table with simple functions running the calculations.
The initial process had 7 text boxes for data entry.
Each control had both hours and minutes entered, example: 7.2, and called the function
Expand|Select|Wrap|Line Numbers
  1.  Public Function OC_Add_Hours()
  2. 'Dim ri, pfh, tth, pht, ht, postht, at, th As Variant
  3.  
  4. 'ri = Forms![attorney statements]![releasee interview]
  5. 'pfh = Forms![attorney statements]![prep for hrg]
  6. 'tth = Forms![attorney statements]![travel to hrg]
  7. 'pht = Forms![attorney statements]![pre hrg time]
  8. 'ht = Forms![attorney statements]![hear time]
  9. 'postht = Forms![attorney statements]![post hrg time]
  10. 'at = Forms![attorney statements]![appeals time]
  11. 'th = Forms![Attorney Statements]![total hours]
  12.  
  13. 'th = ri + pfh + tth + pht + ht + postht + at 
  14.  
  15. 'Forms![attorney statements]![total hours] = th
  16. End Function 
on the exit property.

In the function, th = "total hours" and this control holds the result. The data entered comes from a form sent in from clients with the time they spent working on a case. This time is currently being converted by the person entering the data using a conversion table showing,
"1 = 2 all the way up to 60 = 100". An example on the sheet states, Total hours worked is 6hrs 20 min. The conversion will be 6.33. This confused me!!

They want to get rid of this conversion and just calculate the time by 60 instead of 100.
What I did is separate the hours from the min by creating 7 new controls to capture the minutes. The new form now has the original control "releasee interview" for the hours worked entry and the new control "Rel Interview Min" for the minutes worked capture. I followed this process for all 7 controls. I am no loner calling the hours function and created the routine,
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Rel_Interview_Min_AfterUpdate()
  3. Dim ri, rim, rit  As Long
  4.  
  5. ri = Forms![attorney statements]![releasee interview]
  6. rim = Forms![attorney statements]![Rel Interview Min]
  7. 'rit = Forms![attorney statements]![Rel Interview Total]
  8.  
  9. rit = ri * 60 + rim
  10. Forms![attorney statements]![Rel Interview Total] = rit
  11. End Sub
to run in the AfterUpdate property of all of the minute controls.
Then I created seven controls that will not be visible, using the same naming style, except they end with total to hold this result.
My thinking is to change everything into minutes to be held in the Total min controls and then in the "total hours" control, in the "On Enter" property run this routine:
Expand|Select|Wrap|Line Numbers
  1.  Private Sub total_hours_Enter()
  2. Dim rit, pfht, ttht, phtt, htt, pohtt, att, th As Long
  3.  
  4. rit = Forms![attorney statements]![Rel Interview Total]
  5. pfht = Forms![attorney statements]![Prep for Hrg Total]
  6. ttht = Forms![attorney statements]![Travel to Hrg Total]
  7. phtt = Forms![attorney statements]![Pre Hrg Time total]
  8. htt = Forms![attorney statements]![Hrg Time Total]
  9. pohtt = Forms![attorney statements]![Post Hrg Time Total]
  10. att = Forms![attorney statements]![Appeals Time Total]
  11. 'th = Forms![attorney statements]![total hours]
  12.  
  13. th = rit + pfht + ttht + phtt + htt + pohtt + att
  14. Forms![attorney statements]![total hours] = th
  15. End Sub 
, to get the total minutes,

and in the "On Exit" property run this routine:
Expand|Select|Wrap|Line Numbers
  1.  Private Sub total_hours_Exit(Cancel As Integer)
  2.  
  3. Dim th As Long
  4.  
  5. 'th = Forms![attorney statements]![total hours]
  6.  
  7. th = Forms![attorney statements]![total hours]
  8.  
  9. Forms![attorney statements]![total hours] = th / 60
  10. End Sub 
, to get back to hours and Minutes.

I hope what i am trying to do is clear, i can't seem to figure out how to keep the minutes under 60, and stop the division keeping the remainder.

Any help is greatly appreciated
Aug 16 '07 #1
6 4230
Scott Price
1,384 Recognized Expert Top Contributor
Not sure if this will help you or not, but try this link:

http://office.microsoft.com/en-us/ac...CL100570041033

Regards,
Scott
Aug 16 '07 #2
richbneal
3 New Member
Thanks Scott - I am leaning toward data type and the final formatting of data holding up progress. I will keep trying. Thanks for the link.
Aug 17 '07 #3
Stwange
126 Recognized Expert New Member
6.33 is six and one third hours, which is 6h20.
A simpler way to do this would be to use the existing system, and then add another step to convert it (as the decimal is also a percentage of whole, so multiply it by 60)
Eg. (I haven't test this, but if it doesn't work it should give you an idea)
Private Function convertHours(by val hour as Single) as Single
dim pos as integer
pos = instr(cstr(hour ),".")
if pos <> 0 then
dim fraction as single
fraction = CSng("0" & mid$(CStr(hour) ,pos))
fraction = (fraction / 100) * 60
convertHours = CSng(mid$(CStr( hour),1,pos - 1)) + fraction
end if
End Function

Good Luck. And by the way, It's already past August 1st?
Aug 17 '07 #4
richbneal
3 New Member
6.33 is six and one third hours, which is 6h20.
A simpler way to do this would be to use the existing system, and then add another step to convert it (as the decimal is also a percentage of whole, so multiply it by 60)
Eg. (I haven't test this, but if it doesn't work it should give you an idea)
Private Function convertHours(by val hour as Single) as Single
dim pos as integer
pos = instr(cstr(hour ),".")
if pos <> 0 then
dim fraction as single
fraction = CSng("0" & mid$(CStr(hour) ,pos))
fraction = (fraction / 100) * 60
convertHours = CSng(mid$(CStr( hour),1,pos - 1)) + fraction
end if
End Function

Good Luck. And by the way, It's already past August 1st?
This looks very positive, thank you very much. I guess I am more stressed than i thought. Sept 1st is what i wanted to write.
Aug 17 '07 #5
missinglinq
3,532 Recognized Expert Specialist
Well, duh! From looking at your code, you're apparently working with lawyers! That's enough to stress out a Buddist Monk on Valium!

Welcome to TheScripts!

Linq ;0)>
Aug 17 '07 #6
Scott Price
1,384 Recognized Expert Top Contributor
Well, duh! From looking at your code, you're apparently working with lawyers! That's enough to stress out a Buddist Monk on Valium!

Welcome to TheScripts!

Linq ;0)>
Thanks, Linq!! Made me laugh :-)

I was thinking that lawyers who like to double bill hours probably wouldn't want to implement a system more like a time-clock which would limit them from being able to double bill, but would be MUCH simpler to calculate elapsed time with since the functionality to do so is already built into Access... Didn't want to point out that earlier, though LOL

Regards,
Scott
Aug 17 '07 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

6
9039
by: Ralph Freshour | last post by:
What's a good way to calculate the number of days between two dates in the following format: 2003-07-15 2003-08-02 I've looked at the PHP date functions but I'm still a bit lost...
4
4445
by: Hans Gruber | last post by:
Hi all, I have been struggling with a problem all day, I have been unable to come up with a working solution. I want to write a function which takes 2 unix timestamps and calculates the difference. I want it to return the difference in years, months, days, hours, minutes and seconds (a complete summary). Keeping into account of course...
8
3995
by: King | last post by:
Hi I have following MS Acess query Here is the query ID Name Prgm ID Client ID Date Start Time End Time Minutes C4 Trisha TIP DEK0703 7 /7 /2006 10:00:00 AM 12:00:00 PM 120
2
1979
by: Don | last post by:
How would I get a query to calculate hours? if "Starttime" = 10:00AM and "Quittime" = 04:00PM Thanks, Don.............
3
1846
by: jbosrock | last post by:
Hi to all, Please bear with me as I am newly experienced in basic Access 2003 only. I don't know Visual Basic or macros at all but am attempting to learn on my own. Explanation: Our fleet of trucks have been installed with a GPS tracking system that downloads into csv files all detailed information. The drivers also swipe a card for when...
4
1641
realin
by: realin | last post by:
hi guys.. I have made a function which counts the numbers of days or hours or minutes from the current datetime to the give datetime.. but i am confused while displaying number of days along with number of hours.. rest all works fine(I Suppose). here is the code : function day_counter($date,$msg){ $today=time(); ...
4
1877
by: scott | last post by:
Hi! I have a table with a StartTime and EndTime and want to calculate number of hours worked. Is it possible to get a "Number" answer? E.g. 9:00am (StartTime) worked to 4:00pm (EndTime) = 7 (Hours worked) I would only ever have a single day so it doensn't need to span over different dates.
4
2419
by: Brian | last post by:
I have a 2000/2002 Access db that I use to collect and store my exercisetime using a form to enter. I wanted to see a summary of the total timefor each exercise so I have a subform that does this. Only issue is thatwhen I go over 24 hrs I get the infamous summing time issue. It would bereally easy if Access used the Excel :nn format, but it...
4
3927
by: Missionary2008 | last post by:
Thank you in advance for your help. I'm trying to calculate the Total Time in hours and minutes to complete a job. The way we are calculating it is by taking the Fee paid and dividing that by a 1:00. In Excel - Marsh is charged $90.00 (This is pre-determined) Her job is a $25.00/hour job (This is pre-determined) ...
1
7800
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8086
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6432
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5296
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3737
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3743
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2227
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1325
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1048
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.