|
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!
|
