Calculating Time?!?

[mohsinhq]

My Problem:

I need to calculate the time in Hours/Minutes between 4 values.

Field 1: Holds the Start Date
Field 2: Holds the Start Time

Field 3: Holds the End Date
Field 4: Holds the End Time

any suggestions on how to go about making this work. The End result needs to be in hours/minutes and is likely to be above 24 hrs.

i can only get it to work for 2 values - the End Time minus the Start Time. Having problems with including the date but is vital to the solution.

Thanks in advance

 

[ColinEssex]

It would be better to store both the date and time in the same field then use the DateDiff function to calculate the difference.

Col

 

[KenHigg]

format(datediff(startdate+starttime,enddate+endtime),"hh")

???
ken

 

[The_Doc_Man]

Your problem is that date and time fields are not what they seem to be.

In VBA code, you could perhaps do this with some concatenation.

What you need to do is a multi-step process. BTW, I'm winging this 'cause I don't have the Access Help Files open and I ALWAYS forget the right order of arguments. So any function I name, LOOK IT UP before you completely trust my answer, OK?

First, convert your separate dates and times to distinct date/time strings.

I.e.

Dim DT1 as Date, DT2 as Date
Dim MinDif as Long, HrDiff as Long

DT1 = CDate( Field1 & " " & Field2 )
DT2 = CDate( Field3 & " " & Field4 )

Next, compute the difference between the two in minutes. (You might have to play with this to get the order correct.

MinDif = DateDiff( "m", DT1, DT2 ) 'compute difference in minutes

Now use integer math to separate minutes and hours from each other. I always get this wrong so look up the MODULUS function or operator or whatever it is.

HrDiff = MinDif / 60
MinDif = MinDif MOD 60

(If you don't feel comfortable with MOD, the same result can be obtained in another way...)

MinDif = MinDif - ( 60 * HrDiff )

Now if this is to be displayed, use a format string to back-convert the numbers to the format you want, then concatenate them. Just look up the Format function and the User-Defined integer formats sub-topic. But if this is to be stored, you have two integers right there to be stored as you need.

I'll leave it to you to take it from there.

 

[mohsinhq]

Thanks for all your replies.

I tried to do what you've shown 'the_doc_man' but without luck! im not really a good programmer so ive chosen to take advice from colinessex:

It would be better to store both the date and time in the same field then use the DateDiff function to calculate the difference.

Col

Took your advice and have kept the date and time as one.

=DateDiff("h",[TxtDateRO],[TxtdateDel])

it wont seem to work when i try to include minutes? H:m?

anyone know why or can provide the proper format? (access 97)