I have a database at work to calculate surgical time lengths in minuts. My problem is that when the elapsed time goes past midnight, I get a negative total. The db is reading the two times on the same day. For example: Surgical date 1 Sep 03, start time 2300, stop time 0200. I need this to calculate as 120 minutes, but it comes out as -4260. What can I do?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Time calculations problem 1
- Thread starter rjnitke
- Start date
BobJacksonNCI
Technical User
You have to be storing the complete date and time for start and end. Then, you just need to:
Int(CSng((EndDtTime - StartDtTime) * 24 * 60))
That will give you whole minutes, dropping the remaining seconds.
For further information, refer to Microsoft Knowledgebase article 210276.
Bob
Please view Thread181-473997 for helpful hints regarding this site.
Int(CSng((EndDtTime - StartDtTime) * 24 * 60))
That will give you whole minutes, dropping the remaining seconds.
For further information, refer to Microsoft Knowledgebase article 210276.
Bob
Please view Thread181-473997 for helpful hints regarding this site.
BobJacksonWCom is right. You do need to store complete date time information to do this properly and his solution is sound. You may also consider using the DateDiff function as in
ElapsedTime = DateDiff ('n', StartTime, EndTime)
BTW: 11 PM to 2 AM is 180 minutes, not 120.
ElapsedTime = DateDiff ('n', StartTime, EndTime)
BTW: 11 PM to 2 AM is 180 minutes, not 120.
-
1
- #4
VicLavigne
Technical User
rjnitke, it sounds like you are working on the same project I am working on. I have come across the same problem, and have a solution that doesn't involve entering dates along with the times.
I use a combination of macros based on Expression Builder.
An example is Procedure Time. Procedure time is calculated by Access as EndTime - CutTime, and after all the times are entered into the form I use SetValue to calculate this time.
To compensate for cases starting before MN and ending after MN, I use the following formula:
IIf([EndTime]>[Cut],[EndTime]-[Cut],(TimeValue("23:59"
+[EndTime])-[Cut]+TimeValue("00:01")
It's not real elegant, but it works for me.
I use a combination of macros based on Expression Builder.
An example is Procedure Time. Procedure time is calculated by Access as EndTime - CutTime, and after all the times are entered into the form I use SetValue to calculate this time.
To compensate for cases starting before MN and ending after MN, I use the following formula:
IIf([EndTime]>[Cut],[EndTime]-[Cut],(TimeValue("23:59"
+[EndTime])-[Cut]+TimeValue("00:01")It's not real elegant, but it works for me.
i was looking for a solution to a similiar issue and I used Bob's expression : =Int(CSng((EndDtTime - StartDtTime) * 24 * 60)) and it worked ok.
I am facing 2 problems though :
1. If EndDtTime is null at a particular instant, then result is "error". To avoid this I tried following --
=(0+nz(Int(CSng((EndDtTime - StartDtTime) * 24 * 60)))) but still there was no use.
2. Also I am unable to filter records for a particular date in EndDtTime field using normal commands like "between and"
Any solution to above will be appreciated
I am facing 2 problems though :
1. If EndDtTime is null at a particular instant, then result is "error". To avoid this I tried following --
=(0+nz(Int(CSng((EndDtTime - StartDtTime) * 24 * 60)))) but still there was no use.
2. Also I am unable to filter records for a particular date in EndDtTime field using normal commands like "between and"
Any solution to above will be appreciated
BobJacksonNCI
Technical User
Only have a minute, desikan, so I can't test.
I think nz will work but you need to change the order.
If the usual culprit (null) is EndDtTime, then:
Int(CSng((nz(EndDtTime,0) - StartDtTime) * 24 * 60))
Filtering should also work, you just need to include the time. E.G.,
EndDtTime Between #9/6/03 0:00:00# and #9/7/03 23:59:59#
To include all activity this past weekend.
Refer to Microsoft Knowledgebase article 210276 for more information.
HTH,
Bob
Please view Thread181-473997 for helpful hints regarding this site.
I think nz will work but you need to change the order.
If the usual culprit (null) is EndDtTime, then:
Int(CSng((nz(EndDtTime,0) - StartDtTime) * 24 * 60))
Filtering should also work, you just need to include the time. E.G.,
EndDtTime Between #9/6/03 0:00:00# and #9/7/03 23:59:59#
To include all activity this past weekend.
Refer to Microsoft Knowledgebase article 210276 for more information.
HTH,
Bob
Please view Thread181-473997 for helpful hints regarding this site.
- Thread starter
- #7
I thought my problem was solved with the post from VicLavigne. The expression is exactly what I need, however...I don't think I put it into the db correctly. I replaced the previous expression, DateDiff ("n",[Time In OR]-[Time Out]) and what I got was a number not relating at all to the time. I don't know exactly how to use the SetValue so this must be my problem. I'm trying to learn this stuff using trial/error, the "office assistant" help function, and the Access 2000 Bible. The "Bible" doesn't explain the SetValue in a way that I can understand completely.
BobJacksonNCI
Technical User
rjnitke,
In your first post beginning this thread, you said you needed to calculate minutes and had a problem when start and end times spanned midnight.
Putting the following into a VBA Immediate Window returns 38.
?DateDiff ("n", #9/7/3 23:44:22#, #9/8/3 0:22:55#)
You should be able to substitute your starting date/time for
#9/7/3 23:44:22#
and your ending date/time for
#9/8/3 0:22:55#
to get an accurate result in minutes.
NOTE that secords are dropped, so it could be a minute short.
For this to work, your field types in your table must be date/time. Also note, even if you're primarily interested in the time, those fields are always stored as date + time. See Microsoft Knowledgebase article 210276 for more information.
desikan,
My post to you was meant as a starting point, NOT a solution. Converting a null value to zero and then subtracting a date/time is not likely to prove useful.
You'll have to work out the details - maybe substituting something for the 0 in
Int(CSng((nz(EndDtTime,0) - StartDtTime) * 24 * 60))
Good Luck,
Bob
Please view Thread181-473997 for helpful hints regarding this site.
In your first post beginning this thread, you said you needed to calculate minutes and had a problem when start and end times spanned midnight.
Putting the following into a VBA Immediate Window returns 38.
?DateDiff ("n", #9/7/3 23:44:22#, #9/8/3 0:22:55#)
You should be able to substitute your starting date/time for
#9/7/3 23:44:22#
and your ending date/time for
#9/8/3 0:22:55#
to get an accurate result in minutes.
NOTE that secords are dropped, so it could be a minute short.
For this to work, your field types in your table must be date/time. Also note, even if you're primarily interested in the time, those fields are always stored as date + time. See Microsoft Knowledgebase article 210276 for more information.
desikan,
My post to you was meant as a starting point, NOT a solution. Converting a null value to zero and then subtracting a date/time is not likely to prove useful.
You'll have to work out the details - maybe substituting something for the 0 in
Int(CSng((nz(EndDtTime,0) - StartDtTime) * 24 * 60))
Good Luck,
Bob
Please view Thread181-473997 for helpful hints regarding this site.
- Thread starter
- #9
Thanks to all for the advice. It seems that most people feel that I need to add a second date in order to create an expression that will work for me. I would rather not have to do this since it would require 3 additional fields on my surgical case report and no one likes to type in dates that many times. I would like to try to make VicLavigne's solution work for me, but when I followe the steps, I was unable to use the SetValue function on my calculated control. This is what I had:
Total Surgical Time: DateDiff("n",[Time In OR],[Time Out])
This works great as long as the times are within the same day. As soon as I go past midnight, that's when the problems begin. Is there a way to encorporate Vic's expression of:
IIf([EndTime]>[Cut],[EndTime]-[Cut],(TimeValue("23:59"+[EndTime])-[Cut]+TimeValue("00:01")
into what I already have to acheive the desired result?
Total Surgical Time: DateDiff("n",[Time In OR],[Time Out])
This works great as long as the times are within the same day. As soon as I go past midnight, that's when the problems begin. Is there a way to encorporate Vic's expression of:
IIf([EndTime]>[Cut],[EndTime]-[Cut],(TimeValue("23:59"
+[EndTime])-[Cut]+TimeValue("00:01")into what I already have to acheive the desired result?
BobJacksonNCI
Technical User
I was focusing on long term record keeping and assuming data entered would be stored in a table. Without having actual input stored, it will be more difficult to reconcile data entry errors.
If your start and end "times" are defined as numbers (not dates/times) on your form in military format and will never span two days, you can just do this:
IIF([EndTime] >= [StartTime], [EndTime] - [StartTime], ([EndTime] + 2400) - [StartTime])
In words, if end time is greater or equal to start time give me end time minus start time, otherwise (crossed midnight) add 2400 to end time and give me end time minus start time.
Please view Thread181-473997 for helpful hints regarding this site.
If your start and end "times" are defined as numbers (not dates/times) on your form in military format and will never span two days, you can just do this:
IIF([EndTime] >= [StartTime], [EndTime] - [StartTime], ([EndTime] + 2400) - [StartTime])
In words, if end time is greater or equal to start time give me end time minus start time, otherwise (crossed midnight) add 2400 to end time and give me end time minus start time.
Please view Thread181-473997 for helpful hints regarding this site.
BobJacksonNCI
Technical User
OOPS!
Sorry, small problem - time is not based on the decimal system... If the time fields' format properties on the form are Short Time, the following should work:
IIF([EndTime] >= [StartTime], [EndTime] - [StartTime], ([EndTime] + TimeValue("24:00") - [StartTime])
If the time fields' format properties on the form are defined as numbers, the following should work:
IIF([EndTime] >= [StartTime], TimeValue([EndTime]) - TimeValue([StartTime]), TimeValue([EndTime] + 2400) - TimeValue([StartTime]))
"Sometimes the faster I hurry, the behinder I get"
Bob
Please view Thread181-473997 for helpful hints regarding this site.
Sorry, small problem - time is not based on the decimal system... If the time fields' format properties on the form are Short Time, the following should work:
IIF([EndTime] >= [StartTime], [EndTime] - [StartTime], ([EndTime] + TimeValue("24:00"
) - [StartTime])If the time fields' format properties on the form are defined as numbers, the following should work:
IIF([EndTime] >= [StartTime], TimeValue([EndTime]) - TimeValue([StartTime]), TimeValue([EndTime] + 2400) - TimeValue([StartTime]))
"Sometimes the faster I hurry, the behinder I get"
Bob
Please view Thread181-473997 for helpful hints regarding this site.
Thanks for your reply and I am sorry for my late response.
Your expression for filtering date with time worked beautifully. Thank you very much for the answer.
However, using nz expression when "end dttime" is null, gives answer in the negative and not zero. Though it is logically correct to get a negative answer, I am trying to find whether I can get zero or a blank field when "enddttime" field is null.
Any ideas?
Your expression for filtering date with time worked beautifully. Thank you very much for the answer.
However, using nz expression when "end dttime" is null, gives answer in the negative and not zero. Though it is logically correct to get a negative answer, I am trying to find whether I can get zero or a blank field when "enddttime" field is null.
Any ideas?
- Thread starter
- #14
Bob,
Thanks for the help so far. I really think we are getting close. However....I have tried each of the last expressions you sent, changing the field names to match mine of course, and still no dice. Could it be because I am trying to accomplish this in a report instead of a form? Just wondering.
Thanks for the help so far. I really think we are getting close. However....I have tried each of the last expressions you sent, changing the field names to match mine of course, and still no dice. Could it be because I am trying to accomplish this in a report instead of a form? Just wondering.
BobJacksonNCI
Technical User
You're welcome! Sorry this has taken so long.
Right, I've been making assumptions.
First, I thought we were working with table data.
Then I thought we were working with a form.
Please give specific information.
Do you want a calculated number that will be on a report?
What are the characteristics of the fields that will be used in the calculation? (If being fed by a query, how they're defined there, not what's in the table.)
desikan,
The following should work for you, assuming EndDtTime is either greater than StartDtTime or is null. The nz portion of the expression says to use EndDtTime unless it's null. If so, use StartDtTime, which will result in 0 minutes elapsed.
Int(CSng((nz(EndDtTime,StartDtTime) - StartDtTime) * 24 * 60))
Please view Thread181-473997 for helpful hints regarding this site.
Right, I've been making assumptions.
First, I thought we were working with table data.
Then I thought we were working with a form.
Please give specific information.
Do you want a calculated number that will be on a report?
What are the characteristics of the fields that will be used in the calculation? (If being fed by a query, how they're defined there, not what's in the table.)
desikan,
The following should work for you, assuming EndDtTime is either greater than StartDtTime or is null. The nz portion of the expression says to use EndDtTime unless it's null. If so, use StartDtTime, which will result in 0 minutes elapsed.
Int(CSng((nz(EndDtTime,StartDtTime) - StartDtTime) * 24 * 60))
Please view Thread181-473997 for helpful hints regarding this site.
VicLavigne
Technical User
To F/U my suggestion:
The suggestion by BobJackson: IIF([EndTime] >= [StartTime], TimeValue([EndTime]) - TimeValue([StartTime]), TimeValue([EndTime] + 2400) - TimeValue([StartTime]))
will not work, at least not for me, because Access doesn't recognize 2400 as a real time value. That's why I had to use 23:59 and later add in 00:01 to balance it out.
On my own project, these times are calculated in a Form to add times to the database. So my reports draw the time directly from the tables. ALthough you might be able to have the expression builder create the value for you in a query.
The suggestion by BobJackson: IIF([EndTime] >= [StartTime], TimeValue([EndTime]) - TimeValue([StartTime]), TimeValue([EndTime] + 2400) - TimeValue([StartTime]))
will not work, at least not for me, because Access doesn't recognize 2400 as a real time value. That's why I had to use 23:59 and later add in 00:01 to balance it out.
On my own project, these times are calculated in a Form to add times to the database. So my reports draw the time directly from the tables. ALthough you might be able to have the expression builder create the value for you in a query.
Bob, your answer sounds very logical as you are substituting StartDtTime for 0 whenever EndDtTime is null.
But unfortunately I am still getting error when EndDtTime is null.
Any way, let me try other alternatives and I will come back.
Thanks a lot for your help
But unfortunately I am still getting error when EndDtTime is null.
Any way, let me try other alternatives and I will come back.
Thanks a lot for your help
VicLavigne
Technical User
rjnitke:
I just tried it, and you CAN build this expression in a query for use in a report. Don't worry about using SetValue or other commands. You'll need to use the Expression Builder (or just type the command in) in the Field block of the query, and then add it into the report.
I just tried it, and you CAN build this expression in a query for use in a report. Don't worry about using SetValue or other commands. You'll need to use the Expression Builder (or just type the command in) in the Field block of the query, and then add it into the report.
- Thread starter
- #19
There must be something simple I'm doing wrong here. I put the expression:
IIf([EndTime]>[Cut],[EndTime]-[Cut],(TimeValue("23:59"+[EndTime])-[Cut]+TimeValue("00:01")
In a field on my query. The query draws the numbers from a table where date fields are formated as 'general date'. When I run my report, I get a decimal number like this:
.047361111 and it should be 65
BTW...I am using Access 2002. Does this matter?
IIf([EndTime]>[Cut],[EndTime]-[Cut],(TimeValue("23:59"
+[EndTime])-[Cut]+TimeValue("00:01")In a field on my query. The query draws the numbers from a table where date fields are formated as 'general date'. When I run my report, I get a decimal number like this:
.047361111 and it should be 65
BTW...I am using Access 2002. Does this matter?
BobJacksonNCI
Technical User
rjnitke,
What a hassle, eh?
Let's see...
.047361111 X 1440 = 68 ... (Slight rounding difference)
1440 = 24 * 60 ...
Looks like the result is the decimal portion of a day.
If so, output formatting is all that needs to be added.
Obviously, I don't have date/time calculations mastered.
If you will look at Microsoft Knowledgebase article 210276 -
it will begin to make sense. That is, understanding the format used to store date/time is helpful when working out calculations.
desikan,
Hmmmmmmm,
Are you sure StartDtTime is not null?
Maybe it would be best to make sure both fields have valid date/times before hitting the expression.
Access is wonderful - no matter how simple a problem/question seems ... it isn't!
Bob
Please view Thread181-473997 for helpful hints regarding this site.
What a hassle, eh?
Let's see...
.047361111 X 1440 = 68 ... (Slight rounding difference)
1440 = 24 * 60 ...
Looks like the result is the decimal portion of a day.
If so, output formatting is all that needs to be added.
Obviously, I don't have date/time calculations mastered.
If you will look at Microsoft Knowledgebase article 210276 -
it will begin to make sense. That is, understanding the format used to store date/time is helpful when working out calculations.
desikan,
Hmmmmmmm,
Are you sure StartDtTime is not null?
Maybe it would be best to make sure both fields have valid date/times before hitting the expression.
Access is wonderful - no matter how simple a problem/question seems ... it isn't!
Bob
Please view Thread181-473997 for helpful hints regarding this site.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
