Time difference calculation

[Lance R Peck]

I want to calculate the difference in time between two fields to get a duration.

Start time 01:10:53
Stop time 01:20:05
Duration 00:09:12

How can I achieve this? Or can I?

 

[Neal]

You use the DateDiff function.
In the text box where you want
the duration to appear, enter this:
=Datediff("n",[Start time],[Stop time])

The "n" stands for minutes. "s" is seconds, "h" is hours. You can find the complete list under DateDiff Function in Help.

 

[manishmeta]

Hi,
I am new here. I have a query.
I have a table with many fields in which 2 fields take time values (strt and end time) and 3rd duration field that records the datediff("N", strt, end) i.e diff in minutes.
How to write a code to put those in form I have a form linked up with that table.
In what event I should write this code
duration.text = datediff("n", end.text,strt.text)
I have not done anything like VBA enabling or macro. I dont know how to proceed.
I am new in access but have basics of VB 6.0.

Quick reply is awaited.

 

[ypma]

Manishmta

1. Go into form design
2. Right click the field Duration
3 .Click the Data tab , and control source should be at the top.
Then Just enter the formula provided by Neal .

Your field names seem odd to me as the .text is a VB 6 requirement. unless that is the name of your field you only require to

=Datediff("n",[Strt],[end])
Hope this answers your question

 

[manishmeta]

ypma:

I tried it but its not working. I have two text boxes namely ot (field namepentime) and ct (fieldname: closetime) wherein I enter two times and want the data to be recorded in 3rd text box namely duration (fieldname : duration) but the formula gives the output as
=[Duration]=DateDiff("n",[CloseTime],[OpenTime])
#Type!

 

[Brianwarnock]

Not the whole text just the formula
=DateDiff("n",[CloseTime],[OpenTime])

Brian

 

[ypma]

Providing your text box are date /time. in the control source of the duration all you need is

=DateDiff("n",[CloseTime],[OpenTime])

 

[manishmeta]

Yes. The error let me check the type, it was not date/time ( duration). Even, after changing the datatype stills the same error.

 

[Brianwarnock]

Ah! You can get misled if you join a thread part way through as I did.
Datediff will just return an integer of the value of the type requested eg minutes, or seconds etc.

I now see in post 1 you want more than that in which case you will need a user function. If you search you will find one which I think will return a string of the form you require.

Brian

 

[Brianwarnock]

This might give you a start

http://allenbrowne.com/casu-13.html

Brian

Ps I will probably not be back until next week

 

[Learn2010]

That is where I got my help.

Use DateDiff() to calculate the elapsed time. It returns whole numbers only, so if you want hours and fractions of an hour, you must work in minutes. If you want minutes and seconds, you must get the difference in seconds.

Let's assume a date/time field named StartDateTime to record when the employee clocks on, and another named EndDateTime for when the employee clocks off. To calculate the time worked, create a query into this table, and type this into the Field row of the query design grid:
Minutes: DateDiff("n", [StartDateTime], [EndDateTime])So, what am I missing?

 

[fosh4a]

That is where I got my help.

Use DateDiff() to calculate the elapsed time. It returns whole numbers only, so if you want hours and fractions of an hour, you must work in minutes. If you want minutes and seconds, you must get the difference in seconds.

Let's assume a date/time field named StartDateTime to record when the employee clocks on, and another named EndDateTime for when the employee clocks off. To calculate the time worked, create a query into this table, and type this into the Field row of the query design grid:
Minutes: DateDiff("n", [StartDateTime], [EndDateTime])So, what am I missing?

Hello. Solved same problem.
RideTime: DateDiff("n";[RideStart];[RideEnd])
";" instead of "," worked for me.
Best Regards