Calculating difference in hours between two dates/times

[moogy20]

I apologise in advance for my computer illiteracy. I have a spreadsheet for a surgical study that I am doing and I would like to be able to calculate the time difference in hours please.

The details are as follows:

A2: Date of admission (eg 04/08/2010 in UK terms, ie dd/mm/yyyy)
B2: Time of admission (eg 22:47 -I used a 24 hour clock)
C2: Date of surgery (eg 05/08/2010)
D2: Time of surgery (eg 10:50)

I would like to be able to calculate the difference in hours between these dates/times. Some of the later intervals are more than 24 hours.

Many thanks for any help

 

[T. Valko]

Fantastic. Thanks for all your help! One last thing. I have to do that calculation for a few hundred procedures. Do I have to enter a variant of the formula for each one or is there an easier was to do this?

Thanks

If your data is in a contiguous block something like this:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 121px"><COL style="WIDTH: 120px"><COL style="WIDTH: 106px"><COL style="WIDTH: 106px"><COL style="WIDTH: 74px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Date of admission</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Time of admission</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Date of surgery</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Time of surgery</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Total Time</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/4/2010</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">22:47</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/5/2010</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">10:50</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">12:03</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/9/2010</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8:10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/10/2010</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">13:00</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">28:50</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/22/2010</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">14:15</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/24/2010</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">14:15</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">48:00</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/27/2010</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">7:00</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/27/2010</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">12:45</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">5:45</TD></TR></TBODY></TABLE>


Then you can enter the formula in the first cell, E2, and then just drag copy down the column.

To drag copy...

With the first formula entered and that cell still selected...

With your mouse, left click and hold ("grab") the fill handle. The fill handle is that little black square at the bottom right of the selected cell. When you hover your mouse over the fill handle the cursor will change from a "fat" plus sign to a "skinny" plus sign. When the cursor changes to a skinny plus sign grab the fill handle and just drag down the column as needed.

Another way to copy the formula is to hover the mouse over the fill handle and when it changes to the skinny plus sign left double click the fill handle. This will copy the formula down the column as long as there is a contiguous range of data on either side of the column that contains the formula.

 

[moogy20]

If your data is in a contiguous block something like this:

Sheet1

<table style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 121px"><col style="WIDTH: 120px"><col style="WIDTH: 106px"><col style="WIDTH: 106px"><col style="WIDTH: 74px"></colgroup><tbody><tr style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="HEIGHT: 17px"><td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</td><td style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Date of admission</td><td style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Time of admission</td><td style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Date of surgery</td><td style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Time of surgery</td><td style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Total Time</td></tr><tr style="HEIGHT: 17px"><td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/4/2010</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">22:47</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/5/2010</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">10:50</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">12:03</td></tr><tr style="HEIGHT: 17px"><td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/9/2010</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8:10</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/10/2010</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">13:00</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">28:50</td></tr><tr style="HEIGHT: 17px"><td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/22/2010</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">14:15</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/24/2010</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">14:15</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">48:00</td></tr><tr style="HEIGHT: 17px"><td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/27/2010</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">7:00</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8/27/2010</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">12:45</td><td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">5:45</td></tr></tbody></table>


Then you can enter the formula in the first cell, E2, and then just drag copy down the column.

To drag copy...

With the first formula entered and that cell still selected...

With your mouse, left click and hold ("grab") the fill handle. The fill handle is that little black square at the bottom right of the selected cell. When you hover your mouse over the fill handle the cursor will change from a "fat" plus sign to a "skinny" plus sign. When the cursor changes to a skinny plus sign grab the fill handle and just drag down the column as needed.

Another way to copy the formula is to hover the mouse over the fill handle and when it changes to the skinny plus sign left double click the fill handle. This will copy the formula down the column as long as there is a contiguous range of data on either side of the column that contains the formula.

Brilliant. Thank you so much

 

[T. Valko]

Brilliant. Thank you so much

You're welcome. Thanks for the feedback!

 

[moogy20]

You're welcome. Thanks for the feedback!

Just when you thought I was done.....

I have over 100 results to go through and I want an average of the time. My very basic knowledge stretches to being able to use the average via the auto sum feature.

However, some of the time differences are more than 24 hours in length. For example:

Date of admission and time: 19/01/2010 18:30
Surgery: 21/01/2010 13:15
Time to surgery calculated: 18:45

The time in fact is 42:45

How am I able to get the correct result in the cell and therefore be able to come up with an accurate average please?

Thanks

 

[scottylad2]

maybe this is what you want?

Excel Workbook
	C	D	E
17	01/01/2011 08:45	03/01/2011 17:00	56:15
18	02/01/2011 10:00	06/01/2011 21:45	107:45
19	03/01/2011 13:45	08/01/2011 08:00	114:15
20	19/01/2010 18:30	21/01/2010 13:15	42:45
21		Average	80:15
Sheet1

Excel Workbook
	E
21	80:15
Sheet1

notice the results in column E are formatted as [h]:mm if you would rather have a decimal output, remove the trailing /24 so your sample would show as 42.75 instead of 42 hours 45 mins

hope that helps

 

[moogy20]

maybe this is what you want?

Excel Workbook
	C	D	E
17	01/01/2011 08:45	03/01/2011 17:00	56:15
18	02/01/2011 10:00	06/01/2011 21:45	107:45
19	03/01/2011 13:45	08/01/2011 08:00	114:15
20	19/01/2010 18:30	21/01/2010 13:15	42:45
21		Average	80:15
Sheet1

Excel Workbook
	E
21	80:15
Sheet1

Thanks. I'm guessing the formula will be slightly different if I have separate cells for dates and times?:

B2: Date of admission
C2: Time of admission
D2: Date of surgery
E2: Time of surgery

Cheers

 

[barry houdini]

You still only need simple subtraction as per T.Valko's initial suggestion.

=(D2+E2)-(B2+C2)

The key is the formatting, if you custom format with [h]:mm, including the square brackets then you'll see 42:45 for your example rather than 18:45

You can use a simple average to average the results, too. Again format the cell (with AVERAGE function) as [h]:mm

 

[scottylad2]

As Barry said, T Valkos solution posted earlier will do as you need. It's formatting that you need to correct, press Ctrl + 1 go into the format menu, go to custon and select [h]:mm from those available

Excel Workbook
	C	D	E	F	G
9	Date of admission	Time of admission	Date of surgery	Time of surgery	Total Time [h]:mm
10	04/08/2010	23:00	05/08/2010	11:03	12:03
11	09/08/2010	08:10	10/08/2010	13:00	28:50
12	22/08/2010	14:15	24/08/2010	14:15	48:00
13	26/08/2010	04:15	26/08/2010	10:00	5:45
14			AVERAGE		23:39
Sheet1

Excel Workbook
	G
14	23:39
Sheet1

and a simple average of the total times from the inbuild =Average formula

 

[T. Valko]

Just when you thought I was done.....

I have over 100 results to go through and I want an average of the time. My very basic knowledge stretches to being able to use the average via the auto sum feature.

However, some of the time differences are more than 24 hours in length. For example:

Date of admission and time: 19/01/2010 18:30
Surgery: 21/01/2010 13:15
Time to surgery calculated: 18:45

The time in fact is 42:45

How am I able to get the correct result in the cell and therefore be able to come up with an accurate average please?

Thanks

Sounds like it's just a formatting issue.

As per my other reply:

Select the cell with the formula.
Right click
Select Format Cells
On the Number tab, from the Category drop down list select Custom
In the little box under Type, enter the format code: [h]:mm
OK out

When dealing with time calculations it's usually a good idea to just format the cells as [h]:mm even if the hours will not be greater than 24.

That way you get into the habit of doing it and will have things "covered" when needed!