Elapsed time

[eps]

Hi

Newbie to Access so bare with me.

I am making a database to log working hours onto for different contracts I work on.

The fields I am having a problem with are "start date and time", "End date and time" and then "Elapsed time"

I need to populate these fields in the format 13/08/2006 17:20 etc.

How do I set the date/time format to do this and then how do I calculate the elapsed time in days,hours and minutes ? (perhaps just hours might do instead of days and hours) I also need to take out non working time ie 6pm through to 8am to make the calculations correct.

Everything else works fine bu I am stuck on this now.

Thanks.

 

[gemma-the-husky]

just store the field as a date/time type. The type actually stores a standard MS type which contains the number of milliseconds since a given start date. (ie zero, which is sometime in 1899).

When you use the date/time you can format it as in a number of formats, to include date only, time only or both, with a wide choice of presentation displays.

 

[gemma-the-husky]

you don't need to store the elapsed time - simply do the calculation [end time] - [start time], and display the result. If you do have spaces in your field names you have to enclose them in square brackets to use them. If there are no spaces, then the bracjkets are optional. You will find most people use underscores or dashes as word separators, to avoid syntax problems with missing square brackets

 

[eps]

Gemma

That seems to work ok if I use dd mm yy ie 14/10/06 - 13/10/06 = 1, but if I then put hh mm ss in I get 14:03:40 - 14:03:27 = 0.000150462962962949

What is this all about ??

 

[gemma-the-husky]

I am not sure where you are seeing the result.

you are seeing the result as a decimal fraction of a day, i presume

you need to format your result as a date/time also.

is it in a textbox, if so click properties, and set the format of the text box to date time

if it is in code declare a variable by

dim mytime as date

then mytime = [end time] - [start time] will correctly format the result

 

[The_Doc_Man]

I believe the date-field internal format is actually a DOUBLE that shows the number of DAYS since the system reference date. The difference between two date fields is the exact number of days between them. An expression that does, for example, ( endtime - starttime), will return a DOUBLE.

When the date is stored using DATE(), it stores midnight of that date. If the date is stored using NOW(), it includes the time and date. Therefore, you should be consistent in how you define date/time VALUES.

Another wrinkle is that when there is a difference of greater than 24 hours between two date fields, you need to consider that ordinary formatting routines won't work quite right. (I'm referring to FORMAT$( "ShortDate"...) or stuff like that. You need to work up your own VBA routines or use DatePart to figure out some convenient unit. (I suggest MINUTES.)

You can find other threads in this forum if you search for "Elapsed Time" as a subject.