Select any blank cell says Cell B4 enter the formula AVERAGE --TEXT A2A6-INT A2A6hmm and then press. Click on an empty cell then click on the f x symbol in the function bar above the worksheet. 12 noon for example is 05 and 6pm is 075.
TEXT B2-A2 hmmss.
Click OK and Time format will be set. To calculate the average of cells that meet one criteria use the AVERAGEIF function in Excel. As an office automation tool it allows thousands of calculations to be made in just minutes and simplifies day-to-day work tasks. Select Format Cells and in Number tab choose Time format.