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

[Sheila Preston]

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

 

[Allen Browne]

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.

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

 

[Sheila Preston]

How do I use your control source conversion in the report to add up the total minutes in the format you specified?

 

[Allen Browne]

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.

How do I use your control source conversion in the report to add up the

total minutes in the format you specified?

 

[Sheila]

Thanks a million. It works just the way I need it to.