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
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
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
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.
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")
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
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
Simple, if you are adding small increments, here demonstrates 47 halfhours to 49
23.4999999999998 (ie 47 halfhours is 23.5 hours)
23.9999999999999 (24 hours)
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
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
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.
but it does not take much to test the value is gretaer than one! nor to calculate the
fraction of 24 hours remaining
And if you are working over midnight then
?#02-dec-99 00:30# - #01-dec-99 00:00#
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
time=hours + minutes
So adding incremental times and timesheets becomes easy!