I'm trying to work with a dataset exported from one of our systems. It is a record of physical fitness testing. The system exports can export in a variety of formats (including XLS), but basically kicks out a table of data.
One of the columns is the time that the Soldier took to run two miles, and is formatted as mm:ss. However, when I put this data into Excel, it is inconsistent as to how it formats the data.
For example, the source data is as follows (just the run time column, first 8 of 1174 records):
16:36
15:37
17:01
16:02
14:24
16:36
15:38
16:40
Gets mangled into the following:
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=64 height=20>16 36</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>15 37</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>17 01</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>0.691319</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>0.623264</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>0.714931</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>0.674641</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>16:40
</TD></TR></TBODY></TABLE>
Some of the ones further down are output as Date/Time (i.e., 01-Jan-00 15:36)
I understand that Excel will assume that ##:## is hh:mm, which the first 3 rows are showing. The second 4 rows are the incorrect hh:mm shown as a fraction of a day, then the third row is the 16:40 simply displayed as text. The worst part is that if I save this, Excel has changed the underlying data to whatever it's displaying in the cells. And some of those fractions of a day, when I force them to display as hh:mm:ss, are different than the source data. For example, the first day fraction above (0.6913194) will display as 16:35:30 (hh:mm:ss custom format), and a few minutes with a calculator will confirm that the 0.691... days number is in fact 16 hrs, 35 min, 30 s, which doesn't even match the original 16:02. So not is it only mangling the data display, it is actually changing the underlying information.
I had the database output this data as a .DOC file, and it dutifully gave me a Word table with this column, all in text format. I copied the column over, and it formatted some as hh mm, and some as text. This is a little better, in that at least the underlying information can be retrieved. However, having two different formats in the same column is incredibly inconvenient to work with.
Is there a way to force Excel to work with this data as pure text when I open it? I've got a macro already written that will convert a text 16:40 into 00:16:40 (time format), so this is the least amount of work.
I want to avoid working with mixed formats in this column if it is humanly possible, since that will vastly increase the complexity of programming a macro to convert it into hh:mm:ss format, which Excel can use to find an average time.
One of the columns is the time that the Soldier took to run two miles, and is formatted as mm:ss. However, when I put this data into Excel, it is inconsistent as to how it formats the data.
For example, the source data is as follows (just the run time column, first 8 of 1174 records):
16:36
15:37
17:01
16:02
14:24
16:36
15:38
16:40
Gets mangled into the following:
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=64 height=20>16 36</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>15 37</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>17 01</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>0.691319</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>0.623264</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>0.714931</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>0.674641</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>16:40
</TD></TR></TBODY></TABLE>
Some of the ones further down are output as Date/Time (i.e., 01-Jan-00 15:36)
I understand that Excel will assume that ##:## is hh:mm, which the first 3 rows are showing. The second 4 rows are the incorrect hh:mm shown as a fraction of a day, then the third row is the 16:40 simply displayed as text. The worst part is that if I save this, Excel has changed the underlying data to whatever it's displaying in the cells. And some of those fractions of a day, when I force them to display as hh:mm:ss, are different than the source data. For example, the first day fraction above (0.6913194) will display as 16:35:30 (hh:mm:ss custom format), and a few minutes with a calculator will confirm that the 0.691... days number is in fact 16 hrs, 35 min, 30 s, which doesn't even match the original 16:02. So not is it only mangling the data display, it is actually changing the underlying information.
I had the database output this data as a .DOC file, and it dutifully gave me a Word table with this column, all in text format. I copied the column over, and it formatted some as hh mm, and some as text. This is a little better, in that at least the underlying information can be retrieved. However, having two different formats in the same column is incredibly inconvenient to work with.
Is there a way to force Excel to work with this data as pure text when I open it? I've got a macro already written that will convert a text 16:40 into 00:16:40 (time format), so this is the least amount of work.
I want to avoid working with mixed formats in this column if it is humanly possible, since that will vastly increase the complexity of programming a macro to convert it into hh:mm:ss format, which Excel can use to find an average time.
