Skip to main content
Question

How to show correct Hours and Minutes on Timesheet Summary Report

  • March 12, 2017
  • 0 replies
  • 72 views
We have a timesheet table with hours column and minutes column by person and project and date.  Each person will have multiple records, usually one for morning and another for afternoon.

The hours and minutes are calculated from a StartTime and EndTime and display nicely on the detail report.

The problem is that when using in the Summary report to get number of hours and minutes for each person for each project, the number of hours and  minutes are totally, but the minutes might be 97, 122, etc.  They are summarizing correctly numerically, but we really want to have minutes less than 60 and the number of hours increase correctly.

So, the 97 minutes would add 1 hour to the hours column and the minutes would decrease to 37.

Is there any way to accomplish this?

Thanks in advance for any suggestions.

Dean
 
      This topic has been closed for replies.

      I suggest that you create a new field called Duration which converts the separate hours and minutes into a duration.  

      For example
      Hours([my hours field]) + Minutes([my minutes field])  

      You can either just use that single field on your Summary Report.
      We already have a duration field, but the bookkeeper would like to see a summary expressed in hours and minutes, as this is how it is entered downstream.

      Any other thoughts?
      re: The bookkeeper and any other thoughts.

      Explain to the bookkeeper that 8:45 means 8 hours and 45 minutes.  They are separate numbers separated by a :
      :)  Nice... I am no able to get the duration to display as 8:45.  it is displaying as 8.75.  I tried creating a duration field as well as using the formula above?
      Set the field properties for the Duration field to display with format HH:MM
      Thank you very much.  That is perfect.  I had not used the duration before.  When I said we had duration, it was a numeric field type.
      :)  Thx for letting me know you got it working.
      • MCFNeil
      • Registered
      • March 13, 2017
      >So, the 97 minutes would add 1 hour to the hours column and the minutes would decrease to 37.<

      If you need to separate them out you can have an 2 formula numeric fields for hours and minutes, with the following equations respectively:

      Int(ToHours([End Date / Time]-[Start Date / Time]))


      (Rem(ToHours([End Date / Time]-[Start Date / Time]), 1)*60)

      If you want that to display as "x hours" and "y minutes" you can make them formula text fields and change the formula to these respectively:

      ToText(Int(ToHours([End Date / Time]-[Start Date / Time])))&" hours"

      ToText((Rem(ToHours([End Date / Time]-[Start Date / Time]), 1)*60))&" minutes"
      .... but that would not have worked for a summary report.
      Terms & ConditionsAccessibility statement