Question

Locked

Excel formatting of HH:MM string when hours > 24

By doughtymartin ·
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?

Thanks,
Martin

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Solved

by doughtymartin In reply to Excel formatting of HH:MM ...

I used a Custom format of
[h]:mm

Applied in SAS via:
length cmsg $ 200;
cmsg='[select("c6")]';
put cmsg;
cmsg='[format.number("[h]:mm")]';
put cmsg;

The end result is that the HH:MM strings all appear 'as given' and HH > 23 is displayed without any problems.

Collapse -

[h]:mm should do the job

by UAddUp In reply to Excel formatting of HH:MM ...
Collapse -

I thought only Jack Bauer could solve this one....

by robo_dev In reply to [h]:mm should do the job
Back to Software Forum
4 total posts (Page 1 of 1)  

Software Forums