Time Calculation

[Guest]

I need to calculate the time (in hours) it takes to complete a job function.

I currently have INPUTTime and ENDTime fields in my table that are set-up as
Date/Time - Medium Date.

In my form the INPUTTime field is a hidden field that is not visible and is
defaulted to =Now() so that when opening the form the user does not have to
enter the date/time.

On the same form I have the ENDTime field that the used input the time that
they completed the task in the format 99:00\ >LL;0;_.

I have tried (in a query or unbound text box) using the following
DateDiff("h",[INPUTTime],[ENDTime) calculation to come up with the time it
took to complete the task but come up with wierd results.

I have tried:
"n" for minutes
"d" for days
dividing this by many multiples of days*hours*minutes*seconds, etc.

I do understand that the date/time results are a binary? value that looks
nothong like a date or time, but can be formated in the forms field to the
format you need.

Another issue is that it seems as though the results are increasing with
each record by some variable when I calculate using the DateDiff function.

This is what I see in my current query with Expr2:
DateDiff("h",[ENDTime],[INPUTTime]):

INPUTTime ENDTime Expr2
07/05/07 9:37:24 AM 10:48 AM 942431
07/05/07 10:12:41 AM 11:12 AM 942431
07/05/07 2:02:38 PM 2:23 PM 942432
07/05/07 2:28:15 PM 8:28 AM 942438

1. I would like the expr2 results to show the elapsed hours it took to
complete the job in the form.

2. Is there also an automated way to have the ENDtime field be entered as
the forms CLOSEDate is entered. What I have is a CLOSEDDate filed
"Date/Time" and was wondering how when entering the close date to signify the
record is closed that I could calculate this time from this field?

Thank - Tim

 

[Rick Brandt]

I need to calculate the time (in hours) it takes to complete a job
function.

I currently have INPUTTime and ENDTime fields in my table that are
set-up as Date/Time - Medium Date.

In my form the INPUTTime field is a hidden field that is not visible
and is defaulted to =Now() so that when opening the form the user
does not have to enter the date/time.

On the same form I have the ENDTime field that the used input the
time that they completed the task in the format 99:00\ >LL;0;_.

I have tried (in a query or unbound text box) using the following
DateDiff("h",[INPUTTime],[ENDTime) calculation to come up with the
time it took to complete the task but come up with wierd results.

What was weird? I expect you were confused by how DateDiff works. DateDiff
counts "boundaries crossed". That means that...

DateDiff("h", #2007-07-07 2:59 PM#, #2007-07-07 3:00PM#)

....returns (1) for hour boundaries crossed even though the values are only one
minute apart. In many cases to avoid this you have to use one increment finer
than the interval you actually want and then do the math (rounding as you see
fit).

I have tried:
"n" for minutes
"d" for days
dividing this by many multiples of days*hours*minutes*seconds, etc.

I do understand that the date/time results are a binary? value that
looks nothong like a date or time, but can be formated in the forms
field to the format you need.

Not binary, but rather DateTimes are stored as Double numbers then formatted for
display as desired. Formatting choices have zero influence on the value that is
stored.

Another issue is that it seems as though the results are increasing
with each record by some variable when I calculate using the DateDiff
function.

This is what I see in my current query with Expr2:
DateDiff("h",[ENDTime],[INPUTTime]):

INPUTTime ENDTime Expr2
07/05/07 9:37:24 AM 10:48 AM 942431
07/05/07 10:12:41 AM 11:12 AM 942431
07/05/07 2:02:38 PM 2:23 PM 942432
07/05/07 2:28:15 PM 8:28 AM 942438

Your end time has no date value showing which means Access will use the default
date of December 30, 1899. You MUST include the date in both operands if you
want meaningful results.

1. I would like the expr2 results to show the elapsed hours it took
to complete the job in the form.

Include the date and it will do so.

2. Is there also an automated way to have the ENDtime field be
entered as the forms CLOSEDate is entered. What I have is a
CLOSEDDate filed "Date/Time" and was wondering how when entering the
close date to signify the record is closed that I could calculate
this time from this field?

Why even have two separate fields? Just store both the date and the time in the
CloseDate field and then use that in your calculation.

 

[Bob Quintal]

I need to calculate the time (in hours) it takes to complete a
job function.

I currently have INPUTTime and ENDTime fields in my table that
are set-up as Date/Time - Medium Date.

In my form the INPUTTime field is a hidden field that is not
visible and is defaulted to =Now() so that when opening the
form the user does not have to enter the date/time.

On the same form I have the ENDTime field that the used input
the time that they completed the task in the format 99:00\

LL;0;_.

I have tried (in a query or unbound text box) using the
following DateDiff("h",[INPUTTime],[ENDTime) calculation to
come up with the time it took to complete the task but come up
with wierd results.

I have tried:
"n" for minutes
"d" for days
dividing this by many multiples of days*hours*minutes*seconds,
etc.

I do understand that the date/time results are a binary? value
that looks nothong like a date or time, but can be formated in
the forms field to the format you need.

Another issue is that it seems as though the results are
increasing with each record by some variable when I calculate
using the DateDiff function.

This is what I see in my current query with Expr2:
DateDiff("h",[ENDTime],[INPUTTime]):

INPUTTime ENDTime Expr2
07/05/07 9:37:24 AM 10:48 AM 942431
07/05/07 10:12:41 AM 11:12 AM 942431
07/05/07 2:02:38 PM 2:23 PM 942432
07/05/07 2:28:15 PM 8:28 AM 942438

1. I would like the expr2 results to show the elapsed hours
it took to complete the job in the form.

2. Is there also an automated way to have the ENDtime field
be entered as the forms CLOSEDate is entered. What I have is
a CLOSEDDate filed "Date/Time" and was wondering how when
entering the close date to signify the record is closed that I
could calculate this time from this field?

Thank - Tim

Access stores date/time in a table as the number of days since
December 31,1899 as a double precision number, where the hours
and minutes are the fraction of a day. So noon is .5 of a day.

Datediff will give you your odd numbers since you are putting in
the time on December 31st, 1899, not today's date.

You would find it much easier to store the full date/time in the
end time column.

If you can assume that end time's date is always the same as
start time's date, you can fix the existing end times by adding
the integer portion of inputtime to endtime and get a usable
endtime to difference.

note that datediff returns the integer portion of your selected
increment, so that 3 hours and 59 minutes will return 3, you may
want to calculate in minutes and calculate hours and minutes
from the number of minutes.

Hours = (datediff("n",starttime, enddime)\60 '\indicates integer
division result
Minutes = datediff("n",starttime, enddime) mod 60

Duration = format(hours,"00" &":" & format(minutes."00")

As to tour autopopulate question, if you used now() to populate
your closeddate, you already have the data you need.

 

[Guest]

I need to calculate the time (in hours) it takes to complete a
job function.

I currently have INPUTTime and ENDTime fields in my table that
are set-up as Date/Time - Medium Date.

In my form the INPUTTime field is a hidden field that is not
visible and is defaulted to =Now() so that when opening the
form the user does not have to enter the date/time.

On the same form I have the ENDTime field that the used input
the time that they completed the task in the format 99:00\

LL;0;_.

I have tried (in a query or unbound text box) using the
following DateDiff("h",[INPUTTime],[ENDTime) calculation to
come up with the time it took to complete the task but come up
with wierd results.

I have tried:
"n" for minutes
"d" for days
dividing this by many multiples of days*hours*minutes*seconds,
etc.

I do understand that the date/time results are a binary? value
that looks nothong like a date or time, but can be formated in
the forms field to the format you need.

Another issue is that it seems as though the results are
increasing with each record by some variable when I calculate
using the DateDiff function.

This is what I see in my current query with Expr2:
DateDiff("h",[ENDTime],[INPUTTime]):

INPUTTime ENDTime Expr2
07/05/07 9:37:24 AM 10:48 AM 942431
07/05/07 10:12:41 AM 11:12 AM 942431
07/05/07 2:02:38 PM 2:23 PM 942432
07/05/07 2:28:15 PM 8:28 AM 942438

1. I would like the expr2 results to show the elapsed hours
it took to complete the job in the form.

2. Is there also an automated way to have the ENDtime field
be entered as the forms CLOSEDate is entered. What I have is
a CLOSEDDate filed "Date/Time" and was wondering how when
entering the close date to signify the record is closed that I
could calculate this time from this field?

Thank - Tim

Access stores date/time in a table as the number of days since
December 31,1899 as a double precision number, where the hours
and minutes are the fraction of a day. So noon is .5 of a day.

Datediff will give you your odd numbers since you are putting in
the time on December 31st, 1899, not today's date.

You would find it much easier to store the full date/time in the
end time column.

If you can assume that end time's date is always the same as
start time's date, you can fix the existing end times by adding
the integer portion of inputtime to endtime and get a usable
endtime to difference.

note that datediff returns the integer portion of your selected
increment, so that 3 hours and 59 minutes will return 3, you may
want to calculate in minutes and calculate hours and minutes
from the number of minutes.

Hours = (datediff("n",starttime, enddime)\60 '\indicates integer
division result
Minutes = datediff("n",starttime, enddime) mod 60

Duration = format(hours,"00" &":" & format(minutes."00")

As to tour autopopulate question, if you used now() to populate
your closeddate, you already have the data you need.

My INPUTTime and ENDTime are different dates/times.

If I understand you correctly, the reason my DATEDiff calcuation is not
working out is because the INPUTTime and ENDTime values do not match. If
this is NOT correct please elaborate more.

I beleive my best option at this point is to delete the ENDTime field
because the time information cannot be used to perform any time caluations
based on my INPUTTime field.

If thats true, I would probably be best served to use my CLOSEDate field to
capture the same date/time data as my INPUTTime field?????

But, what I do not understand is that if the user is manually inputting a
date mm/dd/yy, how will the time also be included in their entry to match the
format of the INPUTTime field? I do not want them to have input the time also

I need the user to input a date in the CLOSEDate field to indicate the
record is closed, unless their are other options to show that a record is
closed.

 

[Bob Quintal]

I need to calculate the time (in hours) it takes to
complete a job function.

I currently have INPUTTime and ENDTime fields in my table
that are set-up as Date/Time - Medium Date.

In my form the INPUTTime field is a hidden field that is
not visible and is defaulted to =Now() so that when opening
the form the user does not have to enter the date/time.

On the same form I have the ENDTime field that the used
input the time that they completed the task in the format
99:00\
LL;0;_.

I have tried (in a query or unbound text box) using the
following DateDiff("h",[INPUTTime],[ENDTime) calculation to
come up with the time it took to complete the task but come
up with wierd results.

I have tried:
"n" for minutes
"d" for days
dividing this by many multiples of
days*hours*minutes*seconds, etc.

I do understand that the date/time results are a binary?
value that looks nothong like a date or time, but can be
formated in the forms field to the format you need.

Another issue is that it seems as though the results are
increasing with each record by some variable when I
calculate using the DateDiff function.

This is what I see in my current query with Expr2:
DateDiff("h",[ENDTime],[INPUTTime]):

INPUTTime ENDTime Expr2
07/05/07 9:37:24 AM 10:48 AM 942431
07/05/07 10:12:41 AM 11:12 AM 942431
07/05/07 2:02:38 PM 2:23 PM 942432
07/05/07 2:28:15 PM 8:28 AM 942438

1. I would like the expr2 results to show the elapsed
hours it took to complete the job in the form.

2. Is there also an automated way to have the ENDtime
field be entered as the forms CLOSEDate is entered. What I
have is a CLOSEDDate filed "Date/Time" and was wondering
how when entering the close date to signify the record is
closed that I could calculate this time from this field?

Thank - Tim

Access stores date/time in a table as the number of days
since December 31,1899 as a double precision number, where
the hours and minutes are the fraction of a day. So noon is
.5 of a day.

Datediff will give you your odd numbers since you are putting
in the time on December 31st, 1899, not today's date.

You would find it much easier to store the full date/time in
the end time column.

If you can assume that end time's date is always the same as
start time's date, you can fix the existing end times by
adding the integer portion of inputtime to endtime and get a
usable endtime to difference.

note that datediff returns the integer portion of your
selected increment, so that 3 hours and 59 minutes will
return 3, you may want to calculate in minutes and calculate
hours and minutes from the number of minutes.

Hours = (datediff("n",starttime, enddime)\60 '\indicates
integer division result
Minutes = datediff("n",starttime, enddime) mod 60

Duration = format(hours,"00" &":" & format(minutes."00")

As to tour autopopulate question, if you used now() to
populate your closeddate, you already have the data you need.

My INPUTTime and ENDTime are different dates/times.

If I understand you correctly, the reason my DATEDiff
calcuation is not working out is because the INPUTTime and
ENDTime values do not match. If this is NOT correct please
elaborate more.

I beleive my best option at this point is to delete the
ENDTime field because the time information cannot be used to
perform any time caluations based on my INPUTTime field.

If thats true, I would probably be best served to use my
CLOSEDate field to capture the same date/time data as my
INPUTTime field?????

But, what I do not understand is that if the user is manually
inputting a date mm/dd/yy, how will the time also be included
in their entry to match the format of the INPUTTime field? I
do not want them to have input the time also

I need the user to input a date in the CLOSEDate field to
indicate the record is closed, unless their are other options
to show that a record is closed.

If I understand you correctly, you have inputtime, which
includes the date and time as a single field, and end time and
end date in two separate fields. Is this correct? Tere is hope.

If the endDate has no time, and the endtime has no date, just
adding them using + and not the dateadd() function should give
you a useable value for your calculation

What I do when I need to capture start and end times for job
tracking is to put buttons on the form that puts now() into the
textbox, and still allow the user to edit the time, if for
example they went to lunch and forgot to record the end time
before leaving.

 

[Guest]

in
I need to calculate the time (in hours) it takes to
complete a job function.

I currently have INPUTTime and ENDTime fields in my table
that are set-up as Date/Time - Medium Date.

In my form the INPUTTime field is a hidden field that is
not visible and is defaulted to =Now() so that when opening
the form the user does not have to enter the date/time.

On the same form I have the ENDTime field that the used
input the time that they completed the task in the format
99:00\
LL;0;_.

I have tried (in a query or unbound text box) using the
following DateDiff("h",[INPUTTime],[ENDTime) calculation to
come up with the time it took to complete the task but come
up with wierd results.

I have tried:
"n" for minutes
"d" for days
dividing this by many multiples of
days*hours*minutes*seconds, etc.

I do understand that the date/time results are a binary?
value that looks nothong like a date or time, but can be
formated in the forms field to the format you need.

Another issue is that it seems as though the results are
increasing with each record by some variable when I
calculate using the DateDiff function.

This is what I see in my current query with Expr2:
DateDiff("h",[ENDTime],[INPUTTime]):

INPUTTime ENDTime Expr2
07/05/07 9:37:24 AM 10:48 AM 942431
07/05/07 10:12:41 AM 11:12 AM 942431
07/05/07 2:02:38 PM 2:23 PM 942432
07/05/07 2:28:15 PM 8:28 AM 942438

1. I would like the expr2 results to show the elapsed
hours it took to complete the job in the form.

2. Is there also an automated way to have the ENDtime
field be entered as the forms CLOSEDate is entered. What I
have is a CLOSEDDate filed "Date/Time" and was wondering
how when entering the close date to signify the record is
closed that I could calculate this time from this field?

Thank - Tim

Access stores date/time in a table as the number of days
since December 31,1899 as a double precision number, where
the hours and minutes are the fraction of a day. So noon is
.5 of a day.

Datediff will give you your odd numbers since you are putting
in the time on December 31st, 1899, not today's date.

You would find it much easier to store the full date/time in
the end time column.

If you can assume that end time's date is always the same as
start time's date, you can fix the existing end times by
adding the integer portion of inputtime to endtime and get a
usable endtime to difference.

note that datediff returns the integer portion of your
selected increment, so that 3 hours and 59 minutes will
return 3, you may want to calculate in minutes and calculate
hours and minutes from the number of minutes.

Hours = (datediff("n",starttime, enddime)\60 '\indicates
integer division result
Minutes = datediff("n",starttime, enddime) mod 60

Duration = format(hours,"00" &":" & format(minutes."00")

As to tour autopopulate question, if you used now() to
populate your closeddate, you already have the data you need.

My INPUTTime and ENDTime are different dates/times.

If I understand you correctly, the reason my DATEDiff
calcuation is not working out is because the INPUTTime and
ENDTime values do not match. If this is NOT correct please
elaborate more.

I beleive my best option at this point is to delete the
ENDTime field because the time information cannot be used to
perform any time caluations based on my INPUTTime field.

If thats true, I would probably be best served to use my
CLOSEDate field to capture the same date/time data as my
INPUTTime field?????

But, what I do not understand is that if the user is manually
inputting a date mm/dd/yy, how will the time also be included
in their entry to match the format of the INPUTTime field? I
do not want them to have input the time also

I need the user to input a date in the CLOSEDate field to
indicate the record is closed, unless their are other options
to show that a record is closed.

If I understand you correctly, you have inputtime, which
includes the date and time as a single field, and end time and
end date in two separate fields. Is this correct? Tere is hope.

If the endDate has no time, and the endtime has no date, just
adding them using + and not the dateadd() function should give
you a useable value for your calculation

What I do when I need to capture start and end times for job
tracking is to put buttons on the form that puts now() into the
textbox, and still allow the user to edit the time, if for
example they went to lunch and forgot to record the end time
before leaving.

Are you suggesting that through a formula in my query that I use the enddate
and endtime with the inputdate to arrive at my time calculation? if so what
would an example of that datediff formula look like.

Or, can I add the endate and endtime into a nee field so that from here on
out I have a similar field to the inputdate?

It seems redundant to put a button on the form to input now() into the
field, whe the user is already adding a closedate. Is there a way that when
the user enters the close date the time would also be added without them
typing it in?

 

[Bob Quintal]

:

=?Utf-8?B?dGx5bm4=?= <[email protected]>
wrote in

I need to calculate the time (in hours) it takes to
complete a job function.

I currently have INPUTTime and ENDTime fields in my
table that are set-up as Date/Time - Medium Date.

In my form the INPUTTime field is a hidden field that is
not visible and is defaulted to =Now() so that when
opening the form the user does not have to enter the
date/time.

On the same form I have the ENDTime field that the used
input the time that they completed the task in the
format 99:00\
LL;0;_.

I have tried (in a query or unbound text box) using the
following DateDiff("h",[INPUTTime],[ENDTime) calculation
to come up with the time it took to complete the task
but come up with wierd results.

I have tried:
"n" for minutes
"d" for days
dividing this by many multiples of
days*hours*minutes*seconds, etc.

I do understand that the date/time results are a binary?
value that looks nothong like a date or time, but can be
formated in the forms field to the format you need.

Another issue is that it seems as though the results are
increasing with each record by some variable when I
calculate using the DateDiff function.

This is what I see in my current query with Expr2:
DateDiff("h",[ENDTime],[INPUTTime]):

INPUTTime ENDTime Expr2
07/05/07 9:37:24 AM 10:48 AM 942431
07/05/07 10:12:41 AM 11:12 AM 942431
07/05/07 2:02:38 PM 2:23 PM 942432
07/05/07 2:28:15 PM 8:28 AM 942438

1. I would like the expr2 results to show the elapsed
hours it took to complete the job in the form.

2. Is there also an automated way to have the ENDtime
field be entered as the forms CLOSEDate is entered.
What I have is a CLOSEDDate filed "Date/Time" and was
wondering how when entering the close date to signify
the record is closed that I could calculate this time
from this field?

Thank - Tim

Access stores date/time in a table as the number of days
since December 31,1899 as a double precision number, where
the hours and minutes are the fraction of a day. So noon
is .5 of a day.

Datediff will give you your odd numbers since you are
putting in the time on December 31st, 1899, not today's
date.

You would find it much easier to store the full date/time
in the end time column.

If you can assume that end time's date is always the same
as start time's date, you can fix the existing end times
by adding the integer portion of inputtime to endtime and
get a usable endtime to difference.

note that datediff returns the integer portion of your
selected increment, so that 3 hours and 59 minutes will
return 3, you may want to calculate in minutes and
calculate hours and minutes from the number of minutes.

Hours = (datediff("n",starttime, enddime)\60 '\indicates
integer division result
Minutes = datediff("n",starttime, enddime) mod 60

Duration = format(hours,"00" &":" & format(minutes."00")

As to tour autopopulate question, if you used now() to
populate your closeddate, you already have the data you
need.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from
http://www.teranews.com


My INPUTTime and ENDTime are different dates/times.

If I understand you correctly, the reason my DATEDiff
calcuation is not working out is because the INPUTTime and
ENDTime values do not match. If this is NOT correct please
elaborate more.

I beleive my best option at this point is to delete the
ENDTime field because the time information cannot be used
to perform any time caluations based on my INPUTTime field.

If thats true, I would probably be best served to use my
CLOSEDate field to capture the same date/time data as my
INPUTTime field?????

But, what I do not understand is that if the user is
manually inputting a date mm/dd/yy, how will the time also
be included in their entry to match the format of the
INPUTTime field? I do not want them to have input the time
also

I need the user to input a date in the CLOSEDate field to
indicate the record is closed, unless their are other
options to show that a record is closed.

If I understand you correctly, you have inputtime, which
includes the date and time as a single field, and end time
and end date in two separate fields. Is this correct? Tere is
hope.

If the endDate has no time, and the endtime has no date, just
adding them using + and not the dateadd() function should
give you a useable value for your calculation

What I do when I need to capture start and end times for job
tracking is to put buttons on the form that puts now() into
the textbox, and still allow the user to edit the time, if
for example they went to lunch and forgot to record the end
time before leaving.

Are you suggesting that through a formula in my query that I
use the enddate and endtime with the inputdate to arrive at my
time calculation? if so what would an example of that
datediff formula look like.

Yes, try

Duration: datediff("n",inputdate,(int(enddate+endtime))

and see if the results make sense.as minutes. we can format to
hh:mm later.

Or, can I add the endate and endtime into a nee field so that
from here on out I have a similar field to the inputdate?

It seems redundant to put a button on the form to input now()
into the field, whe the user is already adding a closedate.
Is there a way that when the user enters the close date the
time would also be added without them typing it in?

Why not just set the button to put now in both fields, or
eliminate one since both will contain identical data.

 

[Chris2]

I need to calculate the time (in hours) it takes to complete a job function.

I currently have INPUTTime and ENDTime fields in my table that are set-up as
Date/Time - Medium Date.

In my form the INPUTTime field is a hidden field that is not visible and is
defaulted to =Now() so that when opening the form the user does not have to
enter the date/time.

On the same form I have the ENDTime field that the used input the time that
they completed the task in the format 99:00\ >LL;0;_.

I have tried (in a query or unbound text box) using the following
DateDiff("h",[INPUTTime],[ENDTime) calculation to come up with the time it
took to complete the task but come up with wierd results.

I have tried:
"n" for minutes
"d" for days
dividing this by many multiples of days*hours*minutes*seconds, etc.

I do understand that the date/time results are a binary? value that looks
nothong like a date or time, but can be formated in the forms field to the
format you need.

Another issue is that it seems as though the results are increasing with
each record by some variable when I calculate using the DateDiff function.

This is what I see in my current query with Expr2:
DateDiff("h",[ENDTime],[INPUTTime]):

INPUTTime ENDTime Expr2
07/05/07 9:37:24 AM 10:48 AM 942431
07/05/07 10:12:41 AM 11:12 AM 942431
07/05/07 2:02:38 PM 2:23 PM 942432
07/05/07 2:28:15 PM 8:28 AM 942438

1. I would like the expr2 results to show the elapsed hours it took to
complete the job in the form.

2. Is there also an automated way to have the ENDtime field be entered as
the forms CLOSEDate is entered. What I have is a CLOSEDDate filed
"Date/Time" and was wondering how when entering the close date to signify the
record is closed that I could calculate this time from this field?

Thank - Tim

Tim Lynn,

To display the difference between two Datetime values in hours:minutes:seconds format,
use:

VBA:

Public Function ElapsedTimeFromDatetimes(StartDatetime As Date _
, EndDatetime As Date) As String

' Purpose: Calculate the elapsed time between two
' datetime values.
' Returns: A string in the format of hh:mm:ss
' Errors: "-1", indicates EndDatetime < StartDatetime,
' which cannot happen.


Dim TotalSeconds As Integer ' Total seconds between datetime values.
Dim ElapsedHours As Integer ' Total hours between datetime values.
Dim ElapsedMinutes As Integer ' Total remaining minutes after ElapsedHours.
Dim ElapsedSeconds As Integer ' Total remaining seconds after ElpasedHours
' and ElapsedMinutes.

On Error GoTo ErrorHandler

If EndDatetime < StartDatetime Then

' If EndDatetime too small, return error.

ElapsedTimeFromDatetimes = "-1" ' Error code.

Else

' Calculate elapsed time in hh:mm:ss format.

TotalSeconds = DateDiff("s", StartDatetime, EndDatetime)

ElapsedHours = TotalSeconds \ 3600
ElapsedMinutes = (TotalSeconds \ 60) Mod 60
ElapsedSeconds = TotalSeconds Mod 60

ElapsedTime = Format(CStr(ElapsedHours), "00") & ":" & _
Format(CStr(ElapsedMinutes), "00") & ":" & _
Format(CStr(ElapsedSeconds), "00")

ElapsedTimeFromDatetimes = ElapsedTime

End If

Exit_ElapsedTimeFromDatetimes:

Exit Function

ErrorHandler:

' General error handling. This should probably be
' replaced by a central error handling function.

MsgBox "Error Description: " & Err.Description & vbCr & _
"Error Number: " & Err.Number, vbExclamation, _
"An Error Has Occurred in ElapsedTimeFromDatetimes."

GoTo Exit_ElapsedTimeFromDatetimes:

End Function


The function call would appear as:

ElapsedTimeFromDatetimes([StartDatetime], [EndDatetime])


Expression:

DateDiff("s", [StartDatetime], [EndDatetime]) \ 3600 & ":" & Format(DateDiff("s",
[StartDatetime], [EndDatetime]) \ 60 MOD 60, "00") & ":" & Format(DateDiff("s",
[StartDatetime], [EndDatetime]) MOD 60, "00")

Format(DateDiff("s", #07/03/2007 3:00 AM#, #07/07/2007 5:01 PM#) \ 3600, "00") & ":" &
Format(DateDiff("s", #07/03/2007 3:00 AM#, #07/07/2007 5:01 PM#) \ 60 MOD 60, "00") & ":"
& Format(DateDiff("s", #07/03/2007 3:00 AM#, #07/07/2007 5:01 PM#) MOD 60, "00")


Notes:

Your ending value has only time, and not the date. You will need to update that value
with the correct ending date in order to make the above work.


Sincerely,

Chris O.

 

[Guest]

Yes, my times may be more than a 24 hour period.

What I have done was cut and past my ENDTime data into my CLOSEDate field so
that I can use the INPUTTime fleld and the CLOSEDate field to do my time
calculation, but the results in the Expr1 field are in-accurate (see below)

Expression in my query is Expr1: DateDiff("h",[Input Date],[Date
Closed])

with the following results:

INPUTTime CLOSEDate Expr1
07/05/07 9:37:24 AM 07/05/07 11:12:00 AM 11
07/05/07 10:12:41 AM 07/05/07 11:12:00 AM 11
07/05/07 2:02:38 PM 07/05/07 2:23:00 PM 14
07/05/07 2:28:15 PM 07/06/07 8:28:00 AM 32
07/06/07 7:41:56 AM 07/06/07 7:55:00 AM 7

I need to calculate the time (in hours) it takes to complete a job function.

I currently have INPUTTime and ENDTime fields in my table that are set-up as
Date/Time - Medium Date.

In my form the INPUTTime field is a hidden field that is not visible and is
defaulted to =Now() so that when opening the form the user does not have to
enter the date/time.

On the same form I have the ENDTime field that the used input the time that
they completed the task in the format 99:00\ >LL;0;_.

I have tried (in a query or unbound text box) using the following
DateDiff("h",[INPUTTime],[ENDTime) calculation to come up with the time it
took to complete the task but come up with wierd results.

I have tried:
"n" for minutes
"d" for days
dividing this by many multiples of days*hours*minutes*seconds, etc.

I do understand that the date/time results are a binary? value that looks
nothong like a date or time, but can be formated in the forms field to the
format you need.

Another issue is that it seems as though the results are increasing with
each record by some variable when I calculate using the DateDiff function.

This is what I see in my current query with Expr2:
DateDiff("h",[ENDTime],[INPUTTime]):

INPUTTime ENDTime Expr2
07/05/07 9:37:24 AM 10:48 AM 942431
07/05/07 10:12:41 AM 11:12 AM 942431
07/05/07 2:02:38 PM 2:23 PM 942432
07/05/07 2:28:15 PM 8:28 AM 942438

1. I would like the expr2 results to show the elapsed hours it took to
complete the job in the form.

2. Is there also an automated way to have the ENDtime field be entered as
the forms CLOSEDate is entered. What I have is a CLOSEDDate filed
"Date/Time" and was wondering how when entering the close date to signify the
record is closed that I could calculate this time from this field?

Thank - Tim

Tim Lynn,

To display the difference between two Datetime values in hours:minutes:seconds format,
use:

VBA:

Public Function ElapsedTimeFromDatetimes(StartDatetime As Date _
, EndDatetime As Date) As String

' Purpose: Calculate the elapsed time between two
' datetime values.
' Returns: A string in the format of hh:mm:ss
' Errors: "-1", indicates EndDatetime < StartDatetime,
' which cannot happen.


Dim TotalSeconds As Integer ' Total seconds between datetime values.
Dim ElapsedHours As Integer ' Total hours between datetime values.
Dim ElapsedMinutes As Integer ' Total remaining minutes after ElapsedHours.
Dim ElapsedSeconds As Integer ' Total remaining seconds after ElpasedHours
' and ElapsedMinutes.

On Error GoTo ErrorHandler

If EndDatetime < StartDatetime Then

' If EndDatetime too small, return error.

ElapsedTimeFromDatetimes = "-1" ' Error code.

Else

' Calculate elapsed time in hh:mm:ss format.

TotalSeconds = DateDiff("s", StartDatetime, EndDatetime)

ElapsedHours = TotalSeconds \ 3600
ElapsedMinutes = (TotalSeconds \ 60) Mod 60
ElapsedSeconds = TotalSeconds Mod 60

ElapsedTime = Format(CStr(ElapsedHours), "00") & ":" & _
Format(CStr(ElapsedMinutes), "00") & ":" & _
Format(CStr(ElapsedSeconds), "00")

ElapsedTimeFromDatetimes = ElapsedTime

End If

Exit_ElapsedTimeFromDatetimes:

Exit Function

ErrorHandler:

' General error handling. This should probably be
' replaced by a central error handling function.

MsgBox "Error Description: " & Err.Description & vbCr & _
"Error Number: " & Err.Number, vbExclamation, _
"An Error Has Occurred in ElapsedTimeFromDatetimes."

GoTo Exit_ElapsedTimeFromDatetimes:

End Function


The function call would appear as:

ElapsedTimeFromDatetimes([StartDatetime], [EndDatetime])


Expression:

DateDiff("s", [StartDatetime], [EndDatetime]) \ 3600 & ":" & Format(DateDiff("s",
[StartDatetime], [EndDatetime]) \ 60 MOD 60, "00") & ":" & Format(DateDiff("s",
[StartDatetime], [EndDatetime]) MOD 60, "00")

Format(DateDiff("s", #07/03/2007 3:00 AM#, #07/07/2007 5:01 PM#) \ 3600, "00") & ":" &
Format(DateDiff("s", #07/03/2007 3:00 AM#, #07/07/2007 5:01 PM#) \ 60 MOD 60, "00") & ":"
& Format(DateDiff("s", #07/03/2007 3:00 AM#, #07/07/2007 5:01 PM#) MOD 60, "00")


Notes:

Your ending value has only time, and not the date. You will need to update that value
with the correct ending date in order to make the above work.


Sincerely,

Chris O.

 

[Guest]

I Found the problem - I was using the wrong field in my calculation.

Now, is there a simple way to ignore weekends in this calculation?

Yes, my times may be more than a 24 hour period.

What I have done was cut and past my ENDTime data into my CLOSEDate field so
that I can use the INPUTTime fleld and the CLOSEDate field to do my time
calculation, but the results in the Expr1 field are in-accurate (see below)

Expression in my query is Expr1: DateDiff("h",[Input Date],[Date
Closed])

with the following results:

INPUTTime CLOSEDate Expr1
07/05/07 9:37:24 AM 07/05/07 11:12:00 AM 11
07/05/07 10:12:41 AM 07/05/07 11:12:00 AM 11
07/05/07 2:02:38 PM 07/05/07 2:23:00 PM 14
07/05/07 2:28:15 PM 07/06/07 8:28:00 AM 32
07/06/07 7:41:56 AM 07/06/07 7:55:00 AM 7

I need to calculate the time (in hours) it takes to complete a job function.

I currently have INPUTTime and ENDTime fields in my table that are set-up as
Date/Time - Medium Date.

In my form the INPUTTime field is a hidden field that is not visible and is
defaulted to =Now() so that when opening the form the user does not have to
enter the date/time.

On the same form I have the ENDTime field that the used input the time that
they completed the task in the format 99:00\ >LL;0;_.

I have tried (in a query or unbound text box) using the following
DateDiff("h",[INPUTTime],[ENDTime) calculation to come up with the time it
took to complete the task but come up with wierd results.

I have tried:
"n" for minutes
"d" for days
dividing this by many multiples of days*hours*minutes*seconds, etc.

I do understand that the date/time results are a binary? value that looks
nothong like a date or time, but can be formated in the forms field to the
format you need.

Another issue is that it seems as though the results are increasing with
each record by some variable when I calculate using the DateDiff function.

This is what I see in my current query with Expr2:
DateDiff("h",[ENDTime],[INPUTTime]):

INPUTTime ENDTime Expr2
07/05/07 9:37:24 AM 10:48 AM 942431
07/05/07 10:12:41 AM 11:12 AM 942431
07/05/07 2:02:38 PM 2:23 PM 942432
07/05/07 2:28:15 PM 8:28 AM 942438

1. I would like the expr2 results to show the elapsed hours it took to
complete the job in the form.

2. Is there also an automated way to have the ENDtime field be entered as
the forms CLOSEDate is entered. What I have is a CLOSEDDate filed
"Date/Time" and was wondering how when entering the close date to signify the
record is closed that I could calculate this time from this field?

Thank - Tim

Tim Lynn,

To display the difference between two Datetime values in hours:minutes:seconds format,
use:

VBA:

Public Function ElapsedTimeFromDatetimes(StartDatetime As Date _
, EndDatetime As Date) As String

' Purpose: Calculate the elapsed time between two
' datetime values.
' Returns: A string in the format of hh:mm:ss
' Errors: "-1", indicates EndDatetime < StartDatetime,
' which cannot happen.


Dim TotalSeconds As Integer ' Total seconds between datetime values.
Dim ElapsedHours As Integer ' Total hours between datetime values.
Dim ElapsedMinutes As Integer ' Total remaining minutes after ElapsedHours.
Dim ElapsedSeconds As Integer ' Total remaining seconds after ElpasedHours
' and ElapsedMinutes.

On Error GoTo ErrorHandler

If EndDatetime < StartDatetime Then

' If EndDatetime too small, return error.

ElapsedTimeFromDatetimes = "-1" ' Error code.

Else

' Calculate elapsed time in hh:mm:ss format.

TotalSeconds = DateDiff("s", StartDatetime, EndDatetime)

ElapsedHours = TotalSeconds \ 3600
ElapsedMinutes = (TotalSeconds \ 60) Mod 60
ElapsedSeconds = TotalSeconds Mod 60

ElapsedTime = Format(CStr(ElapsedHours), "00") & ":" & _
Format(CStr(ElapsedMinutes), "00") & ":" & _
Format(CStr(ElapsedSeconds), "00")

ElapsedTimeFromDatetimes = ElapsedTime

End If

Exit_ElapsedTimeFromDatetimes:

Exit Function

ErrorHandler:

' General error handling. This should probably be
' replaced by a central error handling function.

MsgBox "Error Description: " & Err.Description & vbCr & _
"Error Number: " & Err.Number, vbExclamation, _
"An Error Has Occurred in ElapsedTimeFromDatetimes."

GoTo Exit_ElapsedTimeFromDatetimes:

End Function


The function call would appear as:

ElapsedTimeFromDatetimes([StartDatetime], [EndDatetime])


Expression:

DateDiff("s", [StartDatetime], [EndDatetime]) \ 3600 & ":" & Format(DateDiff("s",
[StartDatetime], [EndDatetime]) \ 60 MOD 60, "00") & ":" & Format(DateDiff("s",
[StartDatetime], [EndDatetime]) MOD 60, "00")

Format(DateDiff("s", #07/03/2007 3:00 AM#, #07/07/2007 5:01 PM#) \ 3600, "00") & ":" &
Format(DateDiff("s", #07/03/2007 3:00 AM#, #07/07/2007 5:01 PM#) \ 60 MOD 60, "00") & ":"
& Format(DateDiff("s", #07/03/2007 3:00 AM#, #07/07/2007 5:01 PM#) MOD 60, "00")


Notes:

Your ending value has only time, and not the date. You will need to update that value
with the correct ending date in order to make the above work.


Sincerely,

Chris O.

 

[Chris2]

I Found the problem - I was using the wrong field in my calculation.

Now, is there a simple way to ignore weekends in this calculation?

tlynn,

It depends on what you mean by simple.

Doing WorkDay Math in VBA, http://www.mvps.org/access/datetime/date0012.htm.

You can use these functions to determine if a date is a working day, or not.

You need to determine either what or how many dates aren't working days and subtract them
from the calculation.


Sincerely,

Chris O.