Excel formatting of HH:MM string when hours > 24

0 Votes

Excel formatting of HH:MM string when hours > 24

I'm writing out data to an Excel spreadsheet using a SAS program. Part of this data is a (constructed) text field showing a time in the format HH:MM (eg 23:59).

If the Hours is less that 24 then it displays without any problems and I can average the column using a formula.

If the hours is greater than 24 then it displays as HH:MM:SS (eg 24:01:00) with zeroes in the (unwanted) seconds part of the display. However the averaging still works.

I've tried displaying the cells as text. These display OK but it stops the average formula working.

I've tried displaying the cell as HH:MM, the formulas work but the display still gets messed up for >24 hours.

Does anyone know how I can get round this display problem?