Welcome to SPN

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

Sign Up Now!

Running Count of Hours

Discussion in 'Information Technology' started by RMCDD997, Jul 28, 2006.

  1. RMCDD997

    RMCDD997
    Expand Collapse
    Guest

    I maintain an overtime database for several departments. All hours are
    formatted as date/time (short time) in tables, forms, queries, & reports.
    The report looks fine until the total hours go beyond 24. How can I format my
    hours, so that they will display the correct total hours? My Report should
    display total hours by employee, department, and company total overtime
    hours. Thanks for any assistance.
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    See:
    Calculating elapsed time
    at:
    http://allenbrowne.com/casu-13.html

    The article explains how to use DateDiff() to get the difference in minutes,
    and then display the value as hours and minutes.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "RMCDD997" <RMCDD997@discussions.microsoft.com> wrote in message
    news:F8E84259-55C8-4526-9B8F-BB0E3A4882DF@microsoft.com...
    >I maintain an overtime database for several departments. All hours are
    > formatted as date/time (short time) in tables, forms, queries, & reports.
    > The report looks fine until the total hours go beyond 24. How can I format
    > my
    > hours, so that they will display the correct total hours? My Report
    > should
    > display total hours by employee, department, and company total overtime
    > hours. Thanks for any assistance.
     
  4. RMCDD997

    RMCDD997
    Expand Collapse
    Guest

    Thanks for your timely reply Allen, I really appreciate it. I followed the
    directions on your posting and it worked for part of my report. The
    individual employee hours are counted correctly. One of two departments
    counts correcly, but the other is 30 mins short making my company grand total
    30 mins short as well. I used DateDiff in my query to get all time in
    minutes, which I checked, and are good. I added a textbox in my report to
    convert total minutes to hours & minutes. This worked great for the
    employees. I added textboxs to the remaining two sections. This is what I
    have in the "Data:

    =Sum([Minutes])\60 & Format([Minutes] Mod 60,"\:00") - department total.
    =Sum([Minutes])\60 & Format([Minutes] Mod 60,"\:00") - company grand total

    Any help is always greatly appreciated. Thanks again.

    "Allen Browne" wrote:

    > See:
    > Calculating elapsed time
    > at:
    > http://allenbrowne.com/casu-13.html
    >
    > The article explains how to use DateDiff() to get the difference in minutes,
    > and then display the value as hours and minutes.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "RMCDD997" <RMCDD997@discussions.microsoft.com> wrote in message
    > news:F8E84259-55C8-4526-9B8F-BB0E3A4882DF@microsoft.com...
    > >I maintain an overtime database for several departments. All hours are
    > > formatted as date/time (short time) in tables, forms, queries, & reports.
    > > The report looks fine until the total hours go beyond 24. How can I format
    > > my
    > > hours, so that they will display the correct total hours? My Report
    > > should
    > > display total hours by employee, department, and company total overtime
    > > hours. Thanks for any assistance.

    >
    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    So you are getting the correct number of minutes shown, but then the
    expressions don't display the hours:minutes text correctly?

    I can't imagine why the display would be faulty. I guess it's possible that
    Access somehow misundersood the data type of the Minutes, but I don't see
    why it would work for one and not the other. The idea is:
    Calculated fields misinterpreted
    at:
    http://allenbrowne.com/ser-45.html

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "RMCDD997" <RMCDD997@discussions.microsoft.com> wrote in message
    news:770D67CA-BEBB-4C65-A950-7F0A430D99E9@microsoft.com...
    > Thanks for your timely reply Allen, I really appreciate it. I followed the
    > directions on your posting and it worked for part of my report. The
    > individual employee hours are counted correctly. One of two departments
    > counts correcly, but the other is 30 mins short making my company grand
    > total
    > 30 mins short as well. I used DateDiff in my query to get all time in
    > minutes, which I checked, and are good. I added a textbox in my report to
    > convert total minutes to hours & minutes. This worked great for the
    > employees. I added textboxs to the remaining two sections. This is what I
    > have in the "Data:
    >
    > =Sum([Minutes])\60 & Format([Minutes] Mod 60,"\:00") - department total.
    > =Sum([Minutes])\60 & Format([Minutes] Mod 60,"\:00") - company grand total
    >
    > Any help is always greatly appreciated. Thanks again.
    >
    > "Allen Browne" wrote:
    >
    >> See:
    >> Calculating elapsed time
    >> at:
    >> http://allenbrowne.com/casu-13.html
    >>
    >> The article explains how to use DateDiff() to get the difference in
    >> minutes,
    >> and then display the value as hours and minutes.
    >>
    >> "RMCDD997" <RMCDD997@discussions.microsoft.com> wrote in message
    >> news:F8E84259-55C8-4526-9B8F-BB0E3A4882DF@microsoft.com...
    >> >I maintain an overtime database for several departments. All hours are
    >> > formatted as date/time (short time) in tables, forms, queries, &
    >> > reports.
    >> > The report looks fine until the total hours go beyond 24. How can I
    >> > format
    >> > my
    >> > hours, so that they will display the correct total hours? My Report
    >> > should
    >> > display total hours by employee, department, and company total
    >> > overtime
    >> > hours. Thanks for any assistance.
     
  6. RMCDD997

    RMCDD997
    Expand Collapse
    Guest

    Again, my thanks. I read the additional links and double checked all my work.
    I figured out my mistake. I was using one textbox for all my calculations. I
    ended up creating two textboxes per section, one to display total minutes,
    the other to convert those minutes to "hours:minutes" format. It is awesome.
    Thanks Again.

    "Allen Browne" wrote:

    > So you are getting the correct number of minutes shown, but then the
    > expressions don't display the hours:minutes text correctly?
    >
    > I can't imagine why the display would be faulty. I guess it's possible that
    > Access somehow misundersood the data type of the Minutes, but I don't see
    > why it would work for one and not the other. The idea is:
    > Calculated fields misinterpreted
    > at:
    > http://allenbrowne.com/ser-45.html
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "RMCDD997" <RMCDD997@discussions.microsoft.com> wrote in message
    > news:770D67CA-BEBB-4C65-A950-7F0A430D99E9@microsoft.com...
    > > Thanks for your timely reply Allen, I really appreciate it. I followed the
    > > directions on your posting and it worked for part of my report. The
    > > individual employee hours are counted correctly. One of two departments
    > > counts correcly, but the other is 30 mins short making my company grand
    > > total
    > > 30 mins short as well. I used DateDiff in my query to get all time in
    > > minutes, which I checked, and are good. I added a textbox in my report to
    > > convert total minutes to hours & minutes. This worked great for the
    > > employees. I added textboxs to the remaining two sections. This is what I
    > > have in the "Data:
    > >
    > > =Sum([Minutes])\60 & Format([Minutes] Mod 60,"\:00") - department total.
    > > =Sum([Minutes])\60 & Format([Minutes] Mod 60,"\:00") - company grand total
    > >
    > > Any help is always greatly appreciated. Thanks again.
    > >
    > > "Allen Browne" wrote:
    > >
    > >> See:
    > >> Calculating elapsed time
    > >> at:
    > >> http://allenbrowne.com/casu-13.html
    > >>
    > >> The article explains how to use DateDiff() to get the difference in
    > >> minutes,
    > >> and then display the value as hours and minutes.
    > >>
    > >> "RMCDD997" <RMCDD997@discussions.microsoft.com> wrote in message
    > >> news:F8E84259-55C8-4526-9B8F-BB0E3A4882DF@microsoft.com...
    > >> >I maintain an overtime database for several departments. All hours are
    > >> > formatted as date/time (short time) in tables, forms, queries, &
    > >> > reports.
    > >> > The report looks fine until the total hours go beyond 24. How can I
    > >> > format
    > >> > my
    > >> > hours, so that they will display the correct total hours? My Report
    > >> > should
    > >> > display total hours by employee, department, and company total
    > >> > overtime
    > >> > hours. Thanks for any assistance.

    >
    >
    >
     

Share This Page