how do I calculate elapsed time in Access, I have start date/time.

[Guest]

I have a table set up where we enter the start date and start time, follow up
date and follow up time as well as the close date and close time. How do I
calculate the elapsed time from start to follow up and/or to close?

 

[John Vinson]

I have a table set up where we enter the start date and start time, follow up
date and follow up time as well as the close date and close time. How do I
calculate the elapsed time from start to follow up and/or to close?

Use the DateDiff function. You don't say whether you want this time in
days, hours, seconds, or what; or whether you want the full 24-hour
clock or you want to include only working hours. This can be pretty
simple in the former case, rather complex in the latter!

John W. Vinson[MVP]

 

[Wayne Morgan]

The first thing is to remember that an "elapsed time" is NOT a Date/Time
value. Next, you have to decide how finely you want it reported. If you want
it down to minutes, then calculate the difference in seconds. If you want it
down to hours, then calculate the difference in minutes. Once you have the
difference, format the value as desired. For instance, if you want days,
hours, and minutes then find the difference in seconds and format it as
days, hours and minutes.

To find the difference, use the DateDiff function. This will accept the Date
and Time as a single item, so you may need to concatenate them together if
they are in separate fields.

Example:
Dim ElapsedTimeInSeconds As Long
ElapsedTimeInSeconds = DateDiff("s", "#" & [StartDate] & " " & [StartTime] &
"#", "#" & [EndDate] & " " & [EndTime] & "#")

The #'s are date/time delimiters, similar to using quotes for strings.

To format seconds as Days:Hours:Minutes:

strElapsedTime = ElapsedTimeInSeconds \ 86400 & Format((ElapsedTimeInSeconds
Mod 86400) / 86400, ":hh:nn")

This will truncate the seconds, so 1m59s will show as 1m. If the value was
90,501 seconds, the above would return 1:01:08 for 1 day, 1 hour, 8 minutes.
If you want seconds show also, change the string in the format expression to
":hh:nn:ss".

If you're wondering where 86,400 comes from, it is the number of seconds in
a day. The largest possible long integer (2,147,483,648) gives over 24,000
days when counting in seconds.

 

[Guest]

Wayne, thanks for your assistance. Where do I go to enter information you
provided? Do I do this in a query? I am a basic user of Access and not a
programmer. Any help you can provide will be great.

The first thing is to remember that an "elapsed time" is NOT a Date/Time
value. Next, you have to decide how finely you want it reported. If you want
it down to minutes, then calculate the difference in seconds. If you want it
down to hours, then calculate the difference in minutes. Once you have the
difference, format the value as desired. For instance, if you want days,
hours, and minutes then find the difference in seconds and format it as
days, hours and minutes.

To find the difference, use the DateDiff function. This will accept the Date
and Time as a single item, so you may need to concatenate them together if
they are in separate fields.

Example:
Dim ElapsedTimeInSeconds As Long
ElapsedTimeInSeconds = DateDiff("s", "#" & [StartDate] & " " & [StartTime] &
"#", "#" & [EndDate] & " " & [EndTime] & "#")

The #'s are date/time delimiters, similar to using quotes for strings.

To format seconds as Days:Hours:Minutes:

strElapsedTime = ElapsedTimeInSeconds \ 86400 & Format((ElapsedTimeInSeconds
Mod 86400) / 86400, ":hh:nn")

This will truncate the seconds, so 1m59s will show as 1m. If the value was
90,501 seconds, the above would return 1:01:08 for 1 day, 1 hour, 8 minutes.
If you want seconds show also, change the string in the format expression to
":hh:nn:ss".

If you're wondering where 86,400 comes from, it is the number of seconds in
a day. The largest possible long integer (2,147,483,648) gives over 24,000
days when counting in seconds.

--
Wayne Morgan
MS Access MVP

I have a table set up where we enter the start date and start time, follow
up
date and follow up time as well as the close date and close time. How do
I
calculate the elapsed time from start to follow up and/or to close?

 

[Wayne Morgan]

Where you enter it depends on where you're wanting to use it. It could be a
formula in a calculated control of a query or it could be in VBA code. Of
course, in the query you would leave out the Dim statement and the "variable
=" part of the equations.

--
Wayne Morgan
MS Access MVP

Wayne, thanks for your assistance. Where do I go to enter information you
provided? Do I do this in a query? I am a basic user of Access and not a
programmer. Any help you can provide will be great.

The first thing is to remember that an "elapsed time" is NOT a Date/Time
value. Next, you have to decide how finely you want it reported. If you
want
it down to minutes, then calculate the difference in seconds. If you want
it
down to hours, then calculate the difference in minutes. Once you have
the
difference, format the value as desired. For instance, if you want days,
hours, and minutes then find the difference in seconds and format it as
days, hours and minutes.

To find the difference, use the DateDiff function. This will accept the
Date
and Time as a single item, so you may need to concatenate them together
if
they are in separate fields.

Example:
Dim ElapsedTimeInSeconds As Long
ElapsedTimeInSeconds = DateDiff("s", "#" & [StartDate] & " " &
[StartTime] &
"#", "#" & [EndDate] & " " & [EndTime] & "#")

The #'s are date/time delimiters, similar to using quotes for strings.

To format seconds as Days:Hours:Minutes:

strElapsedTime = ElapsedTimeInSeconds \ 86400 &
Format((ElapsedTimeInSeconds
Mod 86400) / 86400, ":hh:nn")

This will truncate the seconds, so 1m59s will show as 1m. If the value
was
90,501 seconds, the above would return 1:01:08 for 1 day, 1 hour, 8
minutes.
If you want seconds show also, change the string in the format expression
to
":hh:nn:ss".

If you're wondering where 86,400 comes from, it is the number of seconds
in
a day. The largest possible long integer (2,147,483,648) gives over
24,000
days when counting in seconds.

 

[Guest]

I want to see the calculation in a query and use the output in a report. I
don't know how to use VBA codes. I am VERY GREEN when it comes to that
dynamic of Access. Can you tell me how I can enter the formula in the query?
Thanks so much for your help, I hope I'm not being too much of a bother.

Where you enter it depends on where you're wanting to use it. It could be a
formula in a calculated control of a query or it could be in VBA code. Of
course, in the query you would leave out the Dim statement and the "variable
=" part of the equations.

--
Wayne Morgan
MS Access MVP

Wayne, thanks for your assistance. Where do I go to enter information you
provided? Do I do this in a query? I am a basic user of Access and not a
programmer. Any help you can provide will be great.

The first thing is to remember that an "elapsed time" is NOT a Date/Time
value. Next, you have to decide how finely you want it reported. If you
want
it down to minutes, then calculate the difference in seconds. If you want
it
down to hours, then calculate the difference in minutes. Once you have
the
difference, format the value as desired. For instance, if you want days,
hours, and minutes then find the difference in seconds and format it as
days, hours and minutes.

To find the difference, use the DateDiff function. This will accept the
Date
and Time as a single item, so you may need to concatenate them together
if
they are in separate fields.

Example:
Dim ElapsedTimeInSeconds As Long
ElapsedTimeInSeconds = DateDiff("s", "#" & [StartDate] & " " &
[StartTime] &
"#", "#" & [EndDate] & " " & [EndTime] & "#")

The #'s are date/time delimiters, similar to using quotes for strings.

To format seconds as Days:Hours:Minutes:

strElapsedTime = ElapsedTimeInSeconds \ 86400 &
Format((ElapsedTimeInSeconds
Mod 86400) / 86400, ":hh:nn")

This will truncate the seconds, so 1m59s will show as 1m. If the value
was
90,501 seconds, the above would return 1:01:08 for 1 day, 1 hour, 8
minutes.
If you want seconds show also, change the string in the format expression
to
":hh:nn:ss".

If you're wondering where 86,400 comes from, it is the number of seconds
in
a day. The largest possible long integer (2,147,483,648) gives over
24,000
days when counting in seconds.

 

[Wayne Morgan]

Assuming the Start and End date and times are fields in the table, you need
to make a calculated field. This would look something like this in the Field
box in the query design grid. This will give you a resulting field called
Test when you open the query.

Test: (DateDiff("s",[StartDate] & " " & [StartTime],[EndDate] & " " &
[EndTime]))\86400 & Format(((DateDiff("s",[StartDate] & " " &
[StartTime],[EndDate] & " " & [EndTime])) Mod 86400)/86400,":hh:nn")

The values in the brackets are the fields, substitute the correct names. You
may also have to add the table name, for example, instead of [StartDate],
you may need [TableName].[StartDate]. The data type of these fields in the
table needs to be Date/Time.

If these values are parameters instead of fields in the table, you'll need
to add the first line below to the start of the SQL view of the query. This
will define the data type of the parameters.

PARAMETERS StartDate DateTime, StartTime DateTime, EndDate DateTime, EndTime
DateTime;
SELECT ....., etc

I've removed the #'s because a query prefers having the data types defined.
This will be done by defining the type in the table or by using a Parameters
statement. To do this in VBA, you would use the #'s to delimit the values as
Date/Time, similar to using quotes to delimit a string.

--
Wayne Morgan
MS Access MVP

I want to see the calculation in a query and use the output in a report. I
don't know how to use VBA codes. I am VERY GREEN when it comes to that
dynamic of Access. Can you tell me how I can enter the formula in the
query?
Thanks so much for your help, I hope I'm not being too much of a bother.

Where you enter it depends on where you're wanting to use it. It could be
a
formula in a calculated control of a query or it could be in VBA code. Of
course, in the query you would leave out the Dim statement and the
"variable
=" part of the equations.

--
Wayne Morgan
MS Access MVP

Wayne, thanks for your assistance. Where do I go to enter information
you
provided? Do I do this in a query? I am a basic user of Access and
not a
programmer. Any help you can provide will be great.

:

The first thing is to remember that an "elapsed time" is NOT a
Date/Time
value. Next, you have to decide how finely you want it reported. If
you
want
it down to minutes, then calculate the difference in seconds. If you
want
it
down to hours, then calculate the difference in minutes. Once you have
the
difference, format the value as desired. For instance, if you want
days,
hours, and minutes then find the difference in seconds and format it
as
days, hours and minutes.

To find the difference, use the DateDiff function. This will accept
the
Date
and Time as a single item, so you may need to concatenate them
together
if
they are in separate fields.

Example:
Dim ElapsedTimeInSeconds As Long
ElapsedTimeInSeconds = DateDiff("s", "#" & [StartDate] & " " &
[StartTime] &
"#", "#" & [EndDate] & " " & [EndTime] & "#")

The #'s are date/time delimiters, similar to using quotes for strings.

To format seconds as Days:Hours:Minutes:

strElapsedTime = ElapsedTimeInSeconds \ 86400 &
Format((ElapsedTimeInSeconds
Mod 86400) / 86400, ":hh:nn")

This will truncate the seconds, so 1m59s will show as 1m. If the value
was
90,501 seconds, the above would return 1:01:08 for 1 day, 1 hour, 8
minutes.
If you want seconds show also, change the string in the format
expression
to
":hh:nn:ss".

If you're wondering where 86,400 comes from, it is the number of
seconds
in
a day. The largest possible long integer (2,147,483,648) gives over
24,000
days when counting in seconds.

 

[Jess Martyn]

Hi, Pal,

I have recently found a site which claims to be a
major competitor of big-dog Paypal.com

Site is www.greenzap.com/extreme This site is being
launched this summer,2005 on 1st June in full form,
but for now as advertisement strategy, they are
accepting registrations for their site, and you know
what registration is totally free and in addition to
that they are paying $25 just to register. And once
you register, you will have your own promo link like
www.greenzap.com/YOURWORD (like extreme is my word),
and anyone who registers with that link, you will be
paid $5 for each. But to register there, you need to
have a promo link,i.e. to be refered by someone.

So, i have signed up and increasing my community each
day, and I wish same to you.

- regards,
Devang.