Hi!
I have a table with a StartTime and EndTime and want to calculate number of
hours worked. Is it possible to get a "Number" answer?
E.g.
9:00am (StartTime) worked to 4:00pm (EndTime) = 7 (Hours worked)
I would only ever have a single day so it doensn't need to span over
different dates.
Thanks in advance.
The function datediff is your friend in this scenario.
An example is
datediff("H", #09:00#, #17:30#) which would return 8 as a value.
Hope that this helps,
Kind Regards
Anthony Moore
IT Excellence
On Nov 21, 6:53 am, Anthos
> The function datediff is your friend in this scenario.
>
> An example is
> datediff("H", #09:00#, #17:30#) which would return 8 as a value.
>
> Hope that this helps,
>
> Kind Regards
> Anthony Moore
>
> IT Excellence
DateDiff counts boundaries, in this case the number of times the big
hand is at the twelve between the start and stop times.
Unless this is taken into consideration, its return value may not be
as useful as we would like.
Take someone who works from
#9:01:00 AM#
#5:59:00 PM#
He or she works just two minutes short of nine hours.
But DateDiff("H", #9:01:00 AM#, #5:59:00 PM#) returns 8.
If I were being paid for eight hours for working for almost nine hours
I might think I was being treated unfairly.
I have a little function that could be more accurate than a naked
DateDiff; it is UNTESTED AIR CODE but it might serve as a starting
point for the original poster.
Public Function MyHours@( _
ByVal Time1 As Date, _
ByVal Time2 As Date, _
Optional ByVal RoundTo& = 15)
' RoundTo is a number of minutes
' 15 rounds to nearest quarter of an hour
MyHours = Round(CCur(Abs(DateDiff("n", Time1, Time2))) / RoundTo, 0) *
Round(RoundTo / 60, 2)
End Function
For
#9:01:00 AM#
#5:59:00 PM#
MyHours returns 9.
Hi,
you could do something like this
if text1=9:01 and text3=17:57
mytotal = DateDiff("n", Me.Text1, Me.Text3)
myhour = mytotal \ 60 'returns non rounded integer part
myminutes = mytotal Mod 60 'returns remainder
mydisplay = myhour & " hrs and " & myminutes & " minutes"
mydisplay= 8 hrs and 56 minutes
bobh.
On Nov 21, 3:44 am, "scott"
> Hi!
>
> I have a table with a StartTime and EndTime and want to calculate number of
> hours worked. Is it possible to get a "Number" answer?
>
> E.g.
> 9:00am (StartTime) worked to 4:00pm (EndTime) = 7 (Hours worked)
>
> I would only ever have a single day so it doensn't need to span over
> different dates.
>
> Thanks in advance.
Here you go :-)
Sub WorkTime()
mytotal = DateDiff("n", #9:01:00 AM#, #5:56:00 PM#)
myhours = Fix(mytotal / 60)
myminut = mytotal Mod 60
myWTime = myhours & " Hours and " & myminut & " Minutes"
End Sub
this should get you what you need, just swap in some variables where
you need to, or maybe make it a function to return the data you
want....
Cheers
The Frog