Home  |   French  |   About  |   Search  | mvps.org

 What's New Table Of Contents Credits Netiquette 10 Commandments  Bugs Tables Queries Forms Reports Modules APIs Strings Date/Time General Downloads Resources Search Feedback mvps.org
 Date/Time: Time, adding and subtracting time and timesheets Author(s) Robin Stoddart-Stones There has been a recent spate of 'How do I add time', 'Convert to Hours and Minutes' and Bill over 24 Hours and some interesting methods of getting there.     Access97 provides the date field which has some interesting properties.     Setting the date field If you set a date field as Date + Time, it picks up the current system date and time. This provides a very simple date stamp for Timesheet applications. Press the Button and store the current Date and Time in a Datefield.( or a date Variable).( All these examples are copied from the immediate window of a current Access 97 project. )     Thus: Dim lsdt as Date (in the Module) and in the immediate debug window lsdt=Date + Time ? lsdt= 17/02/99 17:10:49. (correct at time of writing)     One DateField can be subtracted from another and the result is the fraction of the day     Thus: lsdt=#16:00#-#15:45# ?lsdt 1.04166666666666E-02 ? (24*60)*1.04166666666666E-02 14.9999999999999 (ie approx 15 minutes to n/1000ths of a second) in other words subtract one time from the other to get the arithmetic answer. Add several of these fractions togther and you get the total time. ?1.04166666666666E-02 +1.04166666666666E-02 2.08333333333332E-02 ?(24*60)*2.08333333333332E-02 29.9999999999998 (Near enough to 30 minutes even if billing as a lawyer!)     If the result is *Under* 24 hours then you can display that total very simply without any maths using format (x,"Short Time")     Thus: ?format(2.08333333333332E-02,"short time") 00:30 which is very useful for billing.     (If your billing rate is £11.50 per hour then your daily rate =24*11.50 and your charge for 30 minutes of work is ?24*11.50 * 2.08333333333332E-02 5.74999999999996 So in one easy to manage datefield you have stored the number of hours worked, one format statement tells you the 'User-eye view' of the time and one simple calculation tells you the cost for under 24 hour totals of work. "Aah! But" I hear you cry, "we have done more than 24 hours of work". Simple, if you are adding small increments, here demonstrates 47 halfhours to 49 halfhours ?47*2.08333333333332E-02 0.97916666666666 ?24* 0.97916666666666 23.4999999999998 (ie 47 halfhours is 23.5 hours) ?48*2.08333333333332E-02 0.999999999999994 ?24*0.999999999999994 23.9999999999999 (24 hours) ?49*2.08333333333332E-02 1.02083333333333 ?24*1.02083333333333 24.4999999999999 (24.5 Hours) now if you are calculating durations (Project managers?) note that you can add this duration of 24.5 hours to a date and find the end date lsdt=#01-dec-99# ?lsdt 01/12/99 lsdt=lsdt+1.02083333333333 ?lsdt 02/12/99 00:30:00 In other words, midnight between 30/nov/99 and 01-Dec-99 + 24.5 hours is halfpast midnight on the 2nd of Dec. (This looks wrong if expressed as midnight 01 Dec 99 but is right) The only problem is that there is no quick format to tell you the number of days and hours if you have gone over the 24. ?format(1.02083333333333,"short Time") 00:30 but it does not take much to test the value is gretaer than one! nor to calculate the fraction of 24 hours remaining lsdt=1.020833333333 days=Int(lsdt) ?days 1 Hours=24*(lsdt-days) ?hours 0.499999999991999 minutes=(hours-int(hours))*60 ?minutes 29.99999999952 And if you are working over midnight then ?#02-dec-99 00:30# - #01-dec-99 00:00# 1.02083333333576 Which is near enough for me at this stage.( The difference is the precision of the machine when multiplied 49 times)(and 49 halfhours came to 1.0208333333) REVERSING the Process: If you are entering time sheets where the Hours and minutes are entered instead of Times then minutes =mins/(24*60) Hours=hrs/24 time=hours + minutes ?#07:30#-#00:00# 0.3125 minutes=30/(24*60) hours=7/24 ?hours+minutes 0.3125 So adding incremental times and timesheets becomes easy!

 © 1998-2010, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer