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
- Follow via:
- RSS
- Email Alert
Question
0
Votes
Answers (2)
0
Votes
Solved
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.
[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.
26th Jun 2008
0
Votes
[h]:mm should do the job
Updated - 25th Jan 2010
Replies
robo_dev
22nd Sep 2009
