How do I insert time in minutes?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table tblDaily_Attendance with the fields
Regular_Work_Hrs
Breaks_Hrs_Allowed
LandD_Hrs_Allowed
all the 3 fields are of Date/Time data types with format=Short time
Regular_Work_Hrs is generally 9 hours
Breaks_Hrs_Allowed is 30 min.
LandD_Hrs_Allowed is 45 min.
I wanted to have a calculation in vba as
Regular_Work_Hrs - ( Breaks_Hrs_Allowed + LandD_Hrs_Allowed ) which would
yeild a result 7:45 hours (i.e. 9hrs - (30min + 45 min))
While entering I want to give the Reqular_Work_Hrs as
9:00,Breaks_Hrs_Allowed as 0:30 and LandD_Hrs_Allowed as 0:45
How do i get this as the format mentioned?
 
Number of hours and minutes should not be confused with time. To perform
math on time, the easiest way to do it is to convert all your times to
minutes, do the math, then covert it back, Not to a time, but to two numbers.
So Reqular_Work_Hrs should be converted from 9:00 to 540 minutes, for example
 
I want to calculate Number of hours and minutes(ie.total working hours -
breaks time) .Then should I take these fields with number data type but while
entering I want to enter them as 9:00 , 0:45 and 0:30
first let me know if the data type I took for these fields is right or
not(i.e. date/time)?
 
I want to calculate Number of hours and minutes(ie.total working hours -
breaks time) .Then should I take these fields with number data type but while
entering I want to enter them as 9:00 , 0:45 and 0:30
first let me know if the data type I took for these fields is right or
not(i.e. date/time)?
Click to expand...

It is NOT right.

Date/Time values are stored as a Double Float number, a count of days
and fractions of a day (times) since an arbitrary start point
(December 30, 1899 at midnight). As such a "time" of 20:00 is stored
as 0.83333333333333333333. Times over 24 hours roll over into the next
day so 26 hours will be displayed as 2:00AM (on December 31, 1899).

Reread klatuu's message. If you want to store 9 hours, store 540 in a
Long Integer field. You can display it as 9:00 with an expression

[Duration] \ 60 & Format([Duration] MOD 60, ":00")

You can enter them by using two textboxes (for hours and minutes) with
some VBA code to calculate the minutes in each textbox's AfterUpdate
event:

Me!txtDuration = NZ(Me!txtHours) * 60 + NZ(Me!txtMinutes)

John W. Vinson[MVP]
 
Reread my post and John's post. I believe you will have a complete answer.
But, to reiterate, Hours and Minutes are not time, they are quantities.
Since VBA uses decimal math, it is necessary to convert to the lowest
practical common denominator. It this case, it is minutes. If you were
keeping track of seconds, then you would need to multiple hours by 360 (the
number of seconds in an hour) and minutes by 60 (the number of seconds in a
minute).

Once you have done the math and want to display the results, you need to use
division to get the hours, minutes, and seconds. The Mod function is well
suited for this.
 
Back
Top