Getting Excel to recognize elapsed time instead of time.

[TomCon]

Trying to paste tabular data into Excel from the web. The web table looks like this.
[TABLE="class: table clickable-table splits-table, width: 100%"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]12:30[/TD]
[TD="align: right"]12:30[/TD]
[TD="align: right"]9:14[/TD]
[TD="align: right"]0.74[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]453[/TD]
[TD="align: right"]3.6[/TD]
[TD="align: right"]4.8[/TD]
[TD="align: right"]22.6[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]78.9[/TD]
[TD="align: right"]63[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]6:10.0[/TD]
[TD="align: right"]18:40[/TD]
[TD="align: right"]3:40[/TD]
[TD="align: right"]0.79[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]413[/TD]
[TD="align: right"]7.7[/TD]
[TD="align: right"]12.9[/TD]
[TD="align: right"]31.9[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]77.0[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]8:50.0[/TD]
[TD="align: right"]27:30[/TD]
[TD="align: right"]8:31[/TD]
[TD="align: right"]1.40[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2,034[/TD]
[TD="align: right"]9.5[/TD]
[TD="align: right"]9.9[/TD]
[TD="align: right"]12.2[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]109[/TD]
[TD="align: right"]75.1[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]7:40.0[/TD]
[TD="align: right"]35:10[/TD]
[TD="align: right"]5:27[/TD]
[TD="align: right"]1.28[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,165[/TD]
[TD="align: right"]10.0[/TD]
[TD="align: right"]14.1[/TD]
[TD="align: right"]29.1[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]73.7[/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]9:40.0[/TD]
[TD="align: right"]44:50[/TD]
[TD="align: right"]7:48[/TD]
[TD="align: right"]1.09[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,165[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]8.4[/TD]
[TD="align: right"]11.6[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]109[/TD]
[TD="align: right"]71.8[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]10:40[/TD]
[TD="align: right"]55:30[/TD]
[TD="align: right"]8:45[/TD]
[TD="align: right"]1.41[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,158[/TD]
[TD="align: right"]7.9[/TD]
[TD="align: right"]9.6[/TD]
[TD="align: right"]26.7[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]71.8[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]11:10[/TD]
[TD="align: right"]1:06:40[/TD]
[TD="align: right"]8:47[/TD]
[TD="align: right"]0.71[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]968[/TD]
[TD="align: right"]3.8[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]14.8[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]67.1[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

The first three columns are "elapsed time" not clock time. So the first column is 12 minutes 30 sec, 6 min 10 sec, etc.

Excel represents the first column as:
12:30:00 AM
12:06:10 AM
12:08:50 AM
12:07:40 AM
12:09:40 AM
10:40:00 AM
11:10:00 AM

The first question is "can i get Excel to just represent these as elapsed time, not clock time?"

Secondly there is a problem. Note that the value in the first row, 12:30 is represented as 12:30:00 AM. But, the value in the second row, 6:10.0 is represented as 12:06:10 AM. Why is this? It looks like values pasted in with a decimal after the seconds, get treated as an offset from 12:00:00 while the others are not. It is a very strange behavior. If it were not for that, i could multiply values by 24 and get the elapsed time in minutes. But in this case, 12:30*24 = 12.50 minutes (correct), but 06:10.0*24 = 0.10, not correct.

Thanks for any help with this that you can provide!

 

[GFelts]

Trying to paste tabular data into Excel from the web. The web table looks like this.
[TABLE="class: table clickable-table splits-table, width: 100%"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]12:30[/TD]
[TD="align: right"]12:30[/TD]
[TD="align: right"]9:14[/TD]
[TD="align: right"]0.74[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]453[/TD]
[TD="align: right"]3.6[/TD]
[TD="align: right"]4.8[/TD]
[TD="align: right"]22.6[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]78.9[/TD]
[TD="align: right"]63[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]6:10.0[/TD]
[TD="align: right"]18:40[/TD]
[TD="align: right"]3:40[/TD]
[TD="align: right"]0.79[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]413[/TD]
[TD="align: right"]7.7[/TD]
[TD="align: right"]12.9[/TD]
[TD="align: right"]31.9[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]77.0[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]8:50.0[/TD]
[TD="align: right"]27:30[/TD]
[TD="align: right"]8:31[/TD]
[TD="align: right"]1.40[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2,034[/TD]
[TD="align: right"]9.5[/TD]
[TD="align: right"]9.9[/TD]
[TD="align: right"]12.2[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]109[/TD]
[TD="align: right"]75.1[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]7:40.0[/TD]
[TD="align: right"]35:10[/TD]
[TD="align: right"]5:27[/TD]
[TD="align: right"]1.28[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,165[/TD]
[TD="align: right"]10.0[/TD]
[TD="align: right"]14.1[/TD]
[TD="align: right"]29.1[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"]73.7[/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]9:40.0[/TD]
[TD="align: right"]44:50[/TD]
[TD="align: right"]7:48[/TD]
[TD="align: right"]1.09[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,165[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]8.4[/TD]
[TD="align: right"]11.6[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]109[/TD]
[TD="align: right"]71.8[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]10:40[/TD]
[TD="align: right"]55:30[/TD]
[TD="align: right"]8:45[/TD]
[TD="align: right"]1.41[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,158[/TD]
[TD="align: right"]7.9[/TD]
[TD="align: right"]9.6[/TD]
[TD="align: right"]26.7[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]71.8[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]11:10[/TD]
[TD="align: right"]1:06:40[/TD]
[TD="align: right"]8:47[/TD]
[TD="align: right"]0.71[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]968[/TD]
[TD="align: right"]3.8[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]14.8[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]67.1[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

The first three columns are "elapsed time" not clock time. So the first column is 12 minutes 30 sec, 6 min 10 sec, etc.

Excel represents the first column as:
12:30:00 AM
12:06:10 AM
12:08:50 AM
12:07:40 AM
12:09:40 AM
10:40:00 AM
11:10:00 AM

The first question is "can i get Excel to just represent these as elapsed time, not clock time?"

Secondly there is a problem. Note that the value in the first row, 12:30 is represented as 12:30:00 AM. But, the value in the second row, 6:10.0 is represented as 12:06:10 AM. Why is this? It looks like values pasted in with a decimal after the seconds, get treated as an offset from 12:00:00 while the others are not. It is a very strange behavior. If it were not for that, i could multiply values by 24 and get the elapsed time in minutes. But in this case, 12:30*24 = 12.50 minutes (correct), but 06:10.0*24 = 0.10, not correct.

Thanks for any help with this that you can provide!

Not 100% what your trying to do but I used Custom Format as [h]:mm:ss So 12:30:00AM becomes 12:30:00. If I enter 12:30:00 into a cell formatted as TIME I get what you have but CUSTOM formatted as [h]:mm:ss I don't. Maybe this will allow you to calculate what you need now.

12:30:00 AM Custom Format [h]:mm:ss equals 12:30:00 X 24 = 300:00:00 / 1440 = 12:12:30 AM

12:06:10 AM Custom Format [h]:mm:ss equals 0:06:10 X 24 = 2:28:00 / 1440 = 12:00:06 AM​

<strike></strike><strike></strike>

12:08:50 AM Custom Format [h]:mm:ss equals 0:08:50 X 24 = 3:32:00 / 1440 = 12:00:09 AM​

<strike></strike><strike></strike>

12:07:40 AM Custom Format [h]:mm:ss equals 0:07:40 X 24 = 3:04:00 / 1440 = 12:00:08 AM​

<strike></strike><strike></strike>

12:09:40 AM Custom Format [h]:mm:ss equals 0:09:40 X 24 = 3:52:00 / 1440 = 12:00:10 AM​

<strike></strike><strike></strike>

10:40:00 AM Custom Format [h]:mm:ss equals 10:40:00 X 24 = 256:00:00 / 1440 = 12:10: 40AM​

<strike></strike><strike></strike>

11:10:00 AM Custom Format [h]:mm:ss equals 11:10:00 X 24 = 268:00:00 / 1440 = 12:11:10 AM<strike></strike>

<strike></strike>
24=Hours in a day---1440=minutes in a day
I think excel is seeing 0:06:10 as being 6 minutes and 10 seconds after midnight or Morning AM.
Hope this helps gets you going.
​

​

<strike style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;"></strike>​

<strike></strike>

 

[TomCon]

Thanks very much for commenting. I appreciate it! This caused me to look more closely and i see one error in my post...but i still have a type of problem. These are all pasted from a web table (much larger than the first 7 rows that i posted here), so i am searching for a "simple, automatic way" to get them converted to an "elapsed time in minutes, as a decimal number" (without re-typing, etc).

The thing i did not see correctly is that Excel treated some of the values as PM and some as AM. I fact, the numbers that start with 2 digits are treated as PM, and the numbers that start with a single digit and have a ".0" at the end are treated as AM.

So, "12:30:00" becomes 12:30PM while "6:10.0" becomes 12:06:10AM.

So, as a decimal 12:30:00PM is .521 and multiplying that by 24 gives me 12.5, what i want, 12.5 minutes of elapsed time.
And, as a decimal 12:06:10AM is .004 and multiplying that by 24 does not give me what i need, which is 6.17.

Just wondered if i can change this Excel behavior upon paste from web, as to how it interprets these values? If not i can probably figure out some conditional formulas that can make the conversion. What i really need is an "elapsed time" concept in Excel, instead of "clock time of day".

Thanks much if you or anybody has thoughts on this!

 

[shg]

So, "12:30:00" becomes 12:30PM while "6:10.0" becomes 12:06:10AM.

Excel doesn't have (or need) the concept of elapsed time vs wall time. 6:10 can be regarded as 6:10 in elapsed time, or 6:10 in the morning.

If you don't want to see AM/PM, format as [h]:mm.

The reason that Excel converts 6:10.0 to 12:06:10 instead of 6:10 AM (or 6:10) is that Excel interprets the decimal as separating whole seconds from decimal seconds. Get rid of the ".0" before importing if that's not what you want.

Multiplying Excel date/time by 24 converts to decimal hours, not decimal minutes.

 

[TomCon]

I am not sure why you say Excel does not need a concept of elapsed time. What all these values are in fact is elapsed time, in minutes. I do not control their format, as they come from a public web site that i do not control. I don't care if they display AM/PM, etc. All i care about is that i can convert them to a consistent decimal number that I can then do additional calculations from. But, i appear to be tripped up by the way that you say that Excel treats that ".0". So, 6:10.0 is converted by Excel to 6:10AM while 12:30 is converted to 12:30PM, and therefore these two elapsed times, which are "very close" (only 6 minutes different) become 12 hours apart. And because of that there is no "simple" way to convert them to elapsed time in minutes. Reformatting to h:mm does not change the underlying value, and so i still cannot do consistent calculations with it, when numbers a few minutes apart appear to Excel to be 12 hours apart.

In summary so far no suggestion to get Excel to treat these as elapsed time in minutes. IF any suggestions out there please let me know. Otherwise, it would look like i need to explore some "conditional formulas" to do the conversion by brute force.

Thanks if any better suggestions!

 

[shg]

But, i appear to be tripped up by the way that you say that Excel treats that ".0". So, 6:10.0 is converted by Excel to 6:10AM while 12:30 is converted to 12:30PM, and therefore these two elapsed times, which are "very close" (only 6 minutes different) become 12 hours apart.

The time difference between 6:10 and 12:30 is 6:20, not 12 hours.

 

[TomCon]

The way Excel reads in 12:30:00, as a decimal, is .521 (as a time displays as 12:30:00 PM). The way Excel reads in 6:10.0 as a decimal is .004 (as a time displays as 12:06:10 AM) . And so the difference between the two is .517 (days) and so in hours it is 12.408 hours. As i explained previously, the intent of the numbers imported from the web is to be "elapsed time in minutes" and so the difference between 6:10 and 12:30 should be approximately 6 minutes, not approximately 12 hours.

I don't know if you will duplicate those results by typing in the numbers. But, pasting them in from a web table, and then simply reformatting them as "number" gives the result, for me, that i state above, .521 and .004)

Actually, i just tried it and there is a way you can duplicate this result. Just select all the rows of the table I put in the original post here in the forum, and copy, and then paste into Excel. Then, look at the first column. You will see that some are interpreted as AM and some as PM. And, if you then format as "number" you will see the values i report here.

Thanks for all the attention put to this intricate detail of how Excel handles numbers!

 

[shg]

The .0 on the end is what causes Excel to interpret the data as m:s, because Excel supports the format mm:ss.00, where the end of that is decimal seconds. Enter =NOW() in a cell and format as m:ss.00 to see an example.

12:30 is always going to be interpreted as h:m.

If all those are intended to be interpreted as m:s, you need to do some fix-up in the source to make all data consistent, or some fix-up in the workbook after the fact.