difference between two times

[Smithinator]

hi i need a formula to find the difference between the start time field and the end time field, i can get the right answer using [end time] - [start time] but it is displayed as a time and but i need it displayed as hours and minutes, what formula should i use to achieve this? i am using access 2010. thanks

 

[Poppa Smurf]

Lookup DateDiff() in the help file.

 

[Smithinator]

i have looked all through help, ive Googled all my questions and have even tried you-tube but everything i try either the answer is wrong or the format is incorrect that is why i have joined this forum because im stuck

 

[Poppa Smurf]

May I suggest you post any code with your question so that you will get a response to your problem without a general response.

 

[Smithinator]

do you mean what comes up when i enter the formula?

 

[vbaInet]

I wonder if the Format() function would be sufficient for this?

 

[Smithinator]

how do you use the format function? where would i put it in this function [end time]-[start time]

 

[vbaInet]

Hmm... maybe not. Have a look at these:

http://www.access-programmers.co.uk/forums/showthread.php?t=162533&highlight=hours+minutes

http://www.access-programmers.co.uk/forums/showthread.php?t=194721&highlight=hours+minutes

 

[Poppa Smurf]

Just a tip, if you scroll down past the posting replies etc. you will come to a section called Similar Threads. These threads may not relate to your version of access but will point you in the right direction.

 

[John Big Booty]

For an explanation of DateDiff() follow the link

 

[Smithinator]

i have tried a few of the suggestions (the ones i understood, ive been using this program for a total of three days) i have got it to display as 8:15 which is the format i want but if you click into the field it shows up as a time e.g. 8:15:00AM, all i need is to find the difference between one time in the start time field e.g. 7:45 and another time in the end time field e.g. 16:00 displayed like this 8:15.

 

[raskew]

Hi -

See if this might be helpful. To use, copy to a standard module and call as shown in the example.

Public Function TimeFHNS(dteFrom As Date, dateTo As Date) As String
'Input: ? TimeFHNS(#07/20/2008 06:30:25 AM#, #07/21/2008 10:30:30 AM#)
'Output: 1 day 4 hours 0 minutes 5 seconds

Dim i As String
Dim x As Date
Dim y As Date
Dim z As Date

x = dteFrom
y = dateTo
z = DateDiff("s", x, y)
'capture days and hours
i = z \ 86400 & " day" & IIf(z \ 86400 <> 1, "s ", " ") & (z Mod 86400) \ 3600 & " hour" & IIf((z Mod 86400) \ 3600 <> 1, "s ", " ")
'capture minutes
i = i & ((z Mod 86400) Mod 3600) \ 60 & " minute" & IIf(((z Mod 86400) Mod 3600) \ 60 <> 1, "s ", " ")
'capture seconds
TimeFHNS = i & ((z Mod 86400) Mod 3600) Mod 60 & " second" & IIf(((z Mod 86400) Mod 3600) Mod 60 <> 1, "s", "")

End Function

HTH - Bob

 

[Smithinator]

im sorry like i said im really new to this program, could you be really specific as i don't know the names of all the feature of access yet. why does it require such large formulas to calculate something that should be really simple?

 

[John Big Booty]

im sorry like i said im really new to this program, could you be really specific as i don't know the names of all the feature of access yet. why does it require such large formulas to calculate something that should be really simple?

DateDiff() will return the difference between two dates/times in any one (and only one) of ten possible units ranging from seconds right through to Years.

Raskew's Module calculates the difference in seconds and then reduces that to Day, Hour, Minutes and Seconds.

 

[Smithinator]

so where do i copy it to?

 

[John Big Booty]

Follow the tutorial here which is from the link in my previous post.

 

[Smithinator]

OK so i open my database, i press alt+F11 then i go insert module, i copy in raskew's text then what do i do?

 

[John Big Booty]

OK now that you have created your Function/Module, on your form put an Unbound Text Box, in it's ControlSource put the following;

=TimeFHNS([FirstTime],[SecondTime])

Change FirstTime and SecondTime to the names of controls in your DB. Check the attached for a practical example

 

[Smithinator]

ok i just want to make sure so when i entered that data into the module i didn't have to name it anything right? because i left it as module1. also could you please be a bit more specific about the next step because i don't know what you mean by unbound text box, in its control source and change first and last time to the names of controls in your DB, sorry i dont know all the correct terms for things yet.

 

[John Big Booty]

First create the Module and paste Raskew's code into it.

Then whenever you need to determine the difference between dates, all you need to do is use;

=TimeFHNS([FirstTime],[SecondTime])

TimeFHNS is the name of the module
Change [FirstTime] to the name of the field/control that holds the start date.
Change [SecondTime] to the name of the field/control that holds the end date.