Home  |  About  |   Search   

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

RunCommand Constants

Terms of Use


 

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-2009, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer