calculate elapse hours and minutes between 2 date and time fields

[bensplace]

I thought this was going to be so simple, how wrong could i have been.

I have 2 date/time fields which have date and time entered in to them
eg. 01/01/1013 10:31:00

I then have a 3rd field which i want to display the elapse time between the first 2 fields.

i managed to get the hours part to work but the minutes were a fraction of the hour not the true minutes of the hour


i can also get it to work if the dates are the same but if i try to go over the 24 hour it falls flat on its face.

please any help, i cant be that hard? can it?

 

[Brianwarnock]

You will need a function to do this and the destination field cannot be a date/time field.

Something like

Function fgettime(sdate As Date, edate As Date) As String
Dim elapsedmins As Long
Dim elapsedhours As Long

elapsedmins = DateDiff("n", sdate, edate)
elapsedhours = Int(elapsedmins / 60)
elapsedmins = elapsedmins - elapsedhours * 60

fgettime = elapsedhours & " : " & elapsedmins

End Function

but note that this has no error checking its merely illustrative

Brian

 

[gemma-the-husky]

as Brian explained, the trouble is that a date/time is held in vba/access as a date portion plus a time portion

access actually stores a date as a number representing the whole number from a date in 1899.

so where there is no date (ie the date is zero), access will actually display the base date of 30/11/1899 (I think, from memory)

the remainder decimal part of a date/time is the time - so 12 noon is half a day, and therefore 0.5 of a day.

if you format the difference as time, it will work for your start time - endtime comparison until either the difference is negative, or until the time exceeds a day - as in both cases all you will see is the time portion of a date/time variable which includes a "date" value as well - which will not be what you want.

so instead, you have to manipulate the date/time VALUE into the format you actually require, manually - as Brian showed you.

 

[ypma]

Using Brianwarnock manipulation of the date/time

Here is another way you might like to consider
txtStartDate 24/02/2013 10:01:41
txtEndDate 24/02/2013 12:31:40
New field as you require hours convert days to Minutes :Unbound
NameofField IE: Minutes Control source =DateDiff("n",[txtstartDate],[txtenddate])
Another field to display your elapsed time: Unbound
Control Source =[Minutes]\60 & "hours" & Format([Minutes] Mod 60,"\:00" & "Minutes")
Show: 2hours:30Minutes
Regards