Time Calculation
tlynn
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.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Bob Quintal
news:0F7B0DFD-6883-4025...@microsoft.com:
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
tlynn
"Bob Quintal" wrote:
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
news:FF3AFFDE-BD6E-4534...@microsoft.com:
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.
tlynn
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
news:61C722E2-A825-46AB...@microsoft.com:
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
"tlynn" <tl...@discussions.microsoft.com> wrote in message
news:0F7B0DFD-6883-4025...@microsoft.com...
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.
tlynn
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
tlynn
Now, is there a simple way to ignore weekends in this calculation?
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.