Time calc problem

[mkelly]

I am trying to get the total time on a form. The time does not exceed 24 hours and I am using short time for my start and finish.

An example is
Start time:9:23 AM Finish time:9:36 AM however my total time returns: 23:46

My formula is:
=[finish time]-[start time]

Any help appriciated

 

[Travis]

use DateDiff

'Returns the number of Minuites between the start and finish times.
=DateDiff("n",[Start Time],[Finish Time])

 

[mkelly]

Is there any way to get hours and minutes?

 

[Travis]

Set the control source to :=fTimeCalc

'Add this to the forms class modual or to a new modual

Public Function fTimeCalc(Start as date,End as Date) as String
Dim iMin as Integer
Dim iHr as Integer

'Get Total Number of Minutes
iMin=DateDiff("n",Start,End)

'Returns only the whole number
iHr=iMin\60

'Returns the Remaining Minutes
iMin=iMin Mod 60

fTimeCalc=iHr & ":" & iMin

End Function

or Set the control source:

=(DateDiff("n",[Start Time],[End Time])\60) & ":" & (DateDiff("n",[Start Time],[End Time]) Mod 60)

 

[R. Hicks]

If only the time values are entered ... and the ending time spans past midnight the result will be incorrect.

Entering the Date and Time stops this problem but if the elapsed time will never span more than 24 hours ... follow the link below to a work around and more info on this problem.

StartTime-EndTime Results in a Negative Number

HTH
RDH

 

[raskew]

Ricky,

A very elegant explanation!

Bob

 

[hair]

Rick, your function just opened my eyes. Thanks a lot.

 

[hair]

actualy there would be a little question
let's say I have a form like

name date starthour finishhour totalhours

as I use the function that you gave us, totalhours is ok but how could I assign for the hours that go over midnight to be automatically took for date+1 ? I mean I need those hours to be calculated as in the next day. The function does the right thing, it returns the right number of hours, but for access the total hours are for the very same day.

 

[R. Hicks]

I don't quite understand your question ...

If the ending time spans past midnight ... it will not be the same day.
You may be referring to "workday" ... but Access does not care about workday when attempting to calculate the elasped time between the two time values.

Technically .. under normal cicumstances ... if the ending value spans past midnight ... the start time is altered to subtract a day from the value .. not add a day to the ending value.

RDH

 

[hair]

well
it's like this (I wasn't very specific)
when I do the totalhours from this table, I need to know when it's holiday (like christmas and stuff) and when it's not. So my query calculates the fields where finishtime is over midnight as if they were in the same day (which is logical). I'd like to have like a function to include this query and to specify that if the finalhour is smaller than startinghour the time over 00:00 goes for the next day. If my very basic way of saying this made the things clear, please tell me if you see a easy solution for this (as a complicated solution sure exists )
Thanks for your time Ricky