How to show correct Hours and Minutes on Timesheet Summary Report

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
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
 
Photo of Dean

Dean

  • 532 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
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.
Photo of Dean

Dean

  • 532 Points 500 badge 2x thumb
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?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
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 :
Photo of Dean

Dean

  • 532 Points 500 badge 2x thumb
:)  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?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
Set the field properties for the Duration field to display with format HH:MM
Photo of Dean

Dean

  • 532 Points 500 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
:)  Thx for letting me know you got it working.
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
>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"
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
.... but that would not have worked for a summary report.