how to add date/time field to get total number of minutes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that the user enters beginning surgery date/time and ending surgery date/time. I decided to use the General Date format for this field instead of just Time since some surgeries occur around midnight and may span to the next day. I then have a field called Duration that is automatically calculated by =[SurgeryEnd]-[SurgeryStart] with a General Date field format. Now the manager of O.R. needs me to create a monthly report that shows total surgery times. I used the SUM function with the General Date format i.e. =SUM[Duration]. The report seemed to be totaling for each procedure that I am subtotaling- but when it got to the fourth subtotal it was way low. The minutes read 12:33 when it should really be over one hour- which I am not sure if maybe the field format is not letting it add past 60 minutes. Any help would be greatly appreciated
 
Create a query into this table.

In the Field row of the query design grid, enter a calcualated field to give
you the number of minutes:
Minutes: DateDiff("n", [SurgeryStart], [SurgeryEnd])

You can now work with this numeric value to perform any calculations you
want.

To display the value as hours and minutes on a form/report, use a text box
with Control Source of:
=([Minutes] \ 60) & Format([Minutes] Mod 60, "\:00")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sheila Preston said:
I have a form that the user enters beginning surgery date/time and ending
Click to expand...
surgery date/time. I decided to use the General Date format for this field
instead of just Time since some surgeries occur around midnight and may span
to the next day. I then have a field called Duration that is automatically
calculated by =[SurgeryEnd]-[SurgeryStart] with a General Date field format.
Now the manager of O.R. needs me to create a monthly report that shows total
surgery times. I used the SUM function with the General Date format i.e.
=SUM[Duration]. The report seemed to be totaling for each procedure that I
am subtotaling- but when it got to the fourth subtotal it was way low. The
minutes read 12:33 when it should really be over one hour- which I am not
sure if maybe the field format is not letting it add past 60 minutes. Any
help would be greatly appreciated
 
How do I use your control source conversion in the report to add up the total minutes in the format you specified?
 
Just the same.

Or, if you wanted to total in the Report Footer section, the Control Source
of the text box would be:

=(Sum([Minutes]) \ 60) & Format(Sum([Minutes]) Mod 60, "\:00")


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sheila Preston said:
How do I use your control source conversion in the report to add up the
Click to expand...
total minutes in the format you specified?
 
Back
Top