time calculation in Access

[Guest]

I have the same problem as another user. I am creating a database to
calculate the time between the beginning and ending of a surgery. When it
occurs past midnight, I can't figure it out. Example: surgery start and
surgery end. Someone had suggested "putting the date and time in the same
field" but I can't figure it out. I have a data entry person identifying the
record on the date the surgery started due to our record keeping but if the
time goes past midnight, I need the total minutes to report on the date the
operation started.

How can I make this work. I need to have specifics so I can get this done
quickly. The boss wants a report by the 7th of november. Thank you. Tina

 

[John Vinson]

I have the same problem as another user. I am creating a database to
calculate the time between the beginning and ending of a surgery. When it
occurs past midnight, I can't figure it out. Example: surgery start and
surgery end. Someone had suggested "putting the date and time in the same
field" but I can't figure it out. I have a data entry person identifying the
record on the date the surgery started due to our record keeping but if the
time goes past midnight, I need the total minutes to report on the date the
operation started.

How can I make this work. I need to have specifics so I can get this done
quickly. The boss wants a report by the 7th of november. Thank you. Tina

An Access Date/Time value is stored as both a date and time - it's
actually stored as a Double Float count of days and fractions of a day
(times) since midnight, December 30, 1899.

If your data entry person enters

10/29/05 10:15pm

in the operation start field, and

10/30/05 1:15am

in the operation end, DateDiff() will correctly calculate taht the
operation lasted just three hours.

If you wish to store the date and time separately, you can use an
expression such as

DateDiff("n", [TimeStarted], [TimeEnded]) + IIF([TimeEnded] <
[TimeStarted], 1440, 0)

to add 24 hours (1440 minutes) for operations that span midnight -
assuming that the operation lasts less than 24 hours, which I sure
hope is the case!

John W. Vinson[MVP]

 

[tina]

if your table has two fields, one for StartDate and one for EndDate, then
set the data type of each field to Date/Time. so a typical record might be

StartDate EndDate
10/29/2005 10:16 PM 10/30/2005 3:14:22 AM

you can use the DateDiff() function to calculate the total minutes, as

DateDiff("n",[StartDate],[EndDate])
which will return 298 (minutes).

if you want to show hours and minutes, use

Int(DateDiff("n",[StartDate],[EndDate])/60) & ":" &
DateDiff("n",[StartDate],[EndDate]) Mod 60
the above goes all on one line, and returns 4:58

hth

I have the same problem as another user. I am creating a database to
calculate the time between the beginning and ending of a surgery. When it
occurs past midnight, I can't figure it out. Example: surgery start and
surgery end. Someone had suggested "putting the date and time in the same
field" but I can't figure it out. I have a data entry person identifying the
record on the date the surgery started due to our record keeping but if the
time goes past midnight, I need the total minutes to report on the date the
operation started.

How can I make this work. I need to have specifics so I can get this done
quickly. The boss wants a report by the 7th of november. Thank you.

Tina

 

[Guest]

Doug Steele has a DateDiff module that works like a charm and is very
straight forward, you can find it by searching on Doug Steele Data Diff in
this newsgroup

 

[Douglas J Steele]

Thanks for the plug, Jeff.

The URL is http://www.accessmvp.com/djsteele/Diff2Dates.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug Steele has a DateDiff module that works like a charm and is very
straight forward, you can find it by searching on Doug Steele Data Diff in
this newsgroup

Tina

 

[Guest]

I am happy to recommend your solutions because they work. Your DateDiff is
used in an application that flags neonatal test results for disease risk
factors based on their age and test results. It is being used in about a
dozen hospitals now. Thanks to you and your fellow MVPs for all the
assistance in the past 10 months.

 

[Diogo Demarchi]

Thanks for the plug, Jeff.

The URL is http://www.accessmvp.com/djsteele/Diff2Dates.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When if date
Tina