Quick Base Discussions

Expand all | Collapse all

How to show correct Hours and Minutes on Timesheet Summary Report

  • 1.  How to show correct Hours and Minutes on Timesheet Summary Report

    Posted 03-12-2017 16:24
    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
     


  • 2.  RE: How to show correct Hours and Minutes on Timesheet Summary Report

    Posted 03-12-2017 16:35
    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.


  • 3.  RE: How to show correct Hours and Minutes on Timesheet Summary Report

    Posted 03-12-2017 16:57
    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?


  • 4.  RE: How to show correct Hours and Minutes on Timesheet Summary Report

    Posted 03-12-2017 17:04
    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 :


  • 5.  RE: How to show correct Hours and Minutes on Timesheet Summary Report

    Posted 03-12-2017 17:14
    :)  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?


  • 6.  RE: How to show correct Hours and Minutes on Timesheet Summary Report

    Posted 03-12-2017 17:18
    Set the field properties for the Duration field to display with format HH:MM


  • 7.  RE: How to show correct Hours and Minutes on Timesheet Summary Report

    Posted 03-12-2017 17:25
    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.


  • 8.  RE: How to show correct Hours and Minutes on Timesheet Summary Report

    Posted 03-12-2017 17:26
    :)  Thx for letting me know you got it working.


  • 9.  RE: How to show correct Hours and Minutes on Timesheet Summary Report

    Top
    Contributor
    Posted 03-13-2017 02:17
    >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"


  • 10.  RE: How to show correct Hours and Minutes on Timesheet Summary Report

    Posted 03-13-2017 11:45
    .... but that would not have worked for a summary report.