Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

time calculation in Access

Discussion in 'Information Technology' started by Tina N., Oct 31, 2005.

  1. Tina N.

    Tina N.
    Expand Collapse
    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
     
  2. Loading...


  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sun, 30 Oct 2005 20:55:02 -0800, "Tina N." <Tina
    N.@discussions.microsoft.com> wrote:

    >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]
     
  4. tina

    tina
    Expand Collapse
    Guest

    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


    "Tina N." <Tina N.@discussions.microsoft.com> wrote in message
    news:816B30CF-28B1-4200-AB28-7FEB27D33355@microsoft.com...
    > 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
     
  5. Jeff C

    Jeff C
    Expand Collapse
    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

    "Tina N." wrote:

    > 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
     
  6. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Thanks for the plug, Jeff.

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

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    news:04FDBF6E-9CCE-4C25-9EF0-360691FDBDD0@microsoft.com...
    > 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 N." wrote:
    >
    > > 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
     
  7. Jeff C

    Jeff C
    Expand Collapse
    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.

    "Douglas J Steele" wrote:

    > Thanks for the plug, Jeff.
    >
    > The URL is http://www.accessmvp.com/djsteele/Diff2Dates.html
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    > news:04FDBF6E-9CCE-4C25-9EF0-360691FDBDD0@microsoft.com...
    > > 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 N." wrote:
    > >
    > > > 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
    >
    >
    >
     
  8. Diogo Demarchi

    Diogo Demarchi
    Expand Collapse
    Guest

    "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:Ovs17Gj3FHA.3628@TK2MSFTNGP10.phx.gbl...
    > Thanks for the plug, Jeff.
    >
    > The URL is http://www.accessmvp.com/djsteele/Diff2Dates.html
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    > news:04FDBF6E-9CCE-4C25-9EF0-360691FDBDD0@microsoft.com...
    > > 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 N." wrote:
    > >
    > > > 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
    >
    >
     

Share This Page