Time difference calculation (2 Viewers)

Lance R Peck · Feb 17, 2000

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 · Feb 17, 2000

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 · Sep 3, 2013

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 · Sep 4, 2013

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 · Sep 4, 2013

ypma:

I tried it but its not working. I have two text boxes namely ot (field name

pentime) 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 · Sep 4, 2013

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

Brian

ypma · Sep 4, 2013

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

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

manishmeta · Sep 4, 2013

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

Brianwarnock · Sep 4, 2013

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 · Sep 4, 2013

This might give you a start

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

Brian

Ps I will probably not be back until next week

Learn2010 · Oct 9, 2014

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 · Jan 17, 2017

Learn2010 said:
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

Time difference calculation (2 Viewers)

Lance R Peck

Registered User.

Neal

Registered User.

manishmeta

Registered User.

ypma

Registered User.

manishmeta

Registered User.

Brianwarnock

Retired

ypma

Registered User.

manishmeta

Registered User.

Brianwarnock

Retired

Brianwarnock

Retired

Learn2010

Registered User.

fosh4a

New member

Similar threads

Users who are viewing this thread