Excel and inputting times

[jeebus1083]

I have a hard time with inputting times into Excel. In order for me to do averages, I have to put the raw seconds in rather than the time conversion. Example: If a race goes in 22.45, 45.56, 57.75 and 1:09.40, I can put the numbers 22.45, 45.56, and 57.75 in, but have to enter the final time as 69.40. This way, I can average off the fractions for specific distances and final times too. Is there a way to actually enter the times so that I do not have to input 1:09.40 as 69.40?

[banacek]

You can enter 109.40 into the column (say X1) and have a column (say Y1) set up (+X1-40) beside it which subtracts 40 off of it (for all rows). Now if you want to do it for a whole bunch of distances, you might want to set up a macro.

[equicom]

Hi... this is actually easier than it seems.

Select the column that you want to enter the time in, and then go to "format"
select custom and type in "M:SS.00"

Those are zero's on the end.

If it doesn't work or you need more help, give me a shout.

[Capper Al]

I have a hard time with inputting times into Excel. In order for me to do averages, I have to put the raw seconds in rather than the time conversion. Example: If a race goes in 22.45, 45.56, 57.75 and 1:09.40, I can put the numbers 22.45, 45.56, and 57.75 in, but have to enter the final time as 69.40. This way, I can average off the fractions for specific distances and final times too. Is there a way to actually enter the times so that I do not have to input 1:09.40 as 69.40?

The easiest solution would be to use two cells for input and a third for the converted time. Example for 1:09.40, type 1 in cell A1 and type 9.4 in cell A2 then in cell A3 use the formula =A1*60+A2 which should give your 69.40 for your handicapping use.

[cj]

Assuming you put the raw time into Block A1, enter this formula to get the time in seconds in any other block:

=IF(A1>=200,A1-80,(IF(A1>=100,A1-40,A1)))

This assumes your times will always be under 3:00.

If you would have slower times, just add a >= 300 in the beginning and subtract 120.

[Light]

Jeeb

Your problem is in your formatting. I agree with equicom except you must input your fractional times as 0:22.45 not 22.45> format cells M:ss.00 or you will get errors. Excel has a tendency to view fractions and decimals as time of day.

[cj]

The important thing to know is how are you inputting the times. Are you importing them from a file, doing it by hand, copying and pasting from another source, etc?

Once we know that, the rest is gravy.

[Capper Al]

Assuming you put the raw time into Block A1, enter this formula to get the time in seconds in any other block:

=IF(A1>=200,A1-80,(IF(A1>=100,A1-40,A1)))

This assumes your times will always be under 3:00.

If you would have slower times, just add a >= 300 in the beginning and subtract 120.

The only assumption here is that your minutes will be under 60. Cell A1 is only for minutes entry while cell A2 is for seconds and fractions of seconds. A3 holds the formula and the result derived by =A1*60+A2.

[Capper Al]

The important thing to know is how are you inputting the times. Are you importing them from a file, doing it by hand, copying and pasting from another source, etc?

Once we know that, the rest is gravy.

Good follow-up. I had assumed they were being typed in.

[Greyfox]

For me banacek's solution is the easiest.
Otherwise, it seems easier for me to do it in my head as I type.
After all the brain is a computer.

[cj]

For me banacek's solution is the easiest.
Otherwise, it seems easier for me to do it in my head as I type.
After all the brain is a computer.

Actually, here is a very simple formula that will work for any time entered:

Assuming Cell A1 is where the time is entered,

=A1-INT(A1/100)*40

[Capper Al]

Actually, here is a very simple formula that will work for any time entered:

Assuming Cell A1 is where the time is entered,

=A1-INT(A1/100)*40

109.4 would translate to 109.4 - (1)*40 = 69.40. This works to display the results in A2.

[cj]

109.4 would translate to 109.4 - (1)*40 = 69.40. This works to display the results in A2.

Well, yes. And it would translate 209.4 to 209.4 - (2)*40 = 129.4. That is the point, it works with any time you enter.

[jeebus1083]

Copying/Pasting from DRF.com's results and Simulcast Daily at first, but am now simply typing them in.

[gl45]

on the subject of time computation:
how would I convert 22.45 45.56 57.75 109.40 into fifth of seconds, using Excel.
Thanks
gl

[equicom]

Well, one fifth of a second is 20 x 100th's, and there are 5 in each second.

So I guess pretty much the formula would be:

bTime = aTime * 5

Therefore: 109.4 seconds = 547 fifths

In an excel sheet, you would just go (assume cell being tested is A1):

=A1*5

Keeping in mind that the ".4" means 40 x 100ths, 4 x 10ths, or 2 x 5ths

[cj]

on the subject of time computation:
how would I convert 22.45 45.56 57.75 109.40 into fifth of seconds, using Excel.
Thanks
gl

Assuming you enter the time in block A1, this Formula will display the time in fifths:

=INT(A1)+((INT((MOD(A1,INT(A1)))*100/20))/10)

[equicom]

I really love to type in things like

=INT(A1)+((INT((MOD(A1,INT(A1)))*100/20))/10)

when I could just type

=A1 * 5

because if I do it the second way, everyone can understand what I am doing. It is the responsibility of everyone who works in the IT industry to ensure that everything is made complicated and difficult. Do not give in. You must do this.

For example, if somebody wants to know how to type in a time, don't (whatever you do!) tell them that all they need to do is format the cell. No! Make sure you tell them to type something in one cell, something else in the other cell, a complicated formula to combine those and do calculations on the numbers... that's better because then we'll all be employed for years. The last thing we want is ordinary people learning how to use their computers, right?

God help us when that awful day comes!

[headhawg]

The last thing we want is ordinary people learning how to use their computers, right?

God help us when that awful day comes!There's a way better chance of you becoming less of an sarcastic ass then of ordinary people learning how to use a computer well enough to not need tech help.

Well, maybe not...

Related to that, the problem with computer users' questions and message boards is one of clarity. Let's look at gl45's request, shall we?

on the subject of time computation:
how would I convert 22.45 45.56 57.75 109.40 into fifth of seconds, using Excel.
Thanks
glWhat does he/she really want to compute? Each fraction into fifths or does gl45 want the decimal portion in each cell to be converted to fifths -- 22.45 into 22.2?

I assumed the latter.

[gl45]

HHawg,
CJ formula works like a charm.

headhawg, CJ,
thanks for responding
gl

[gl45]

equicom
will you please define ordinary people for me.
gl

[equicom]

Generally the description of an ordinary person would read as follows:

1 x head (several trillion hair foliciles either filled or unfilled, 2 eyes, 1 nose, 1 mouth)

1 x neck

2 x arms (with hands, thumbs, fingers)

1 x torso

2 x legs (with feet and toes)

===

Sorry for sarcasm. Just pointing out that it would be nice if we all kept our answers as simple as possible. That way anybody reading the posts can use the advice, not just the experts.

Also, I interpreted the question differently to the way Mr Hawg did, but that does not make my answer any less correct... it just wasn't the one you were looking for. It did answer the original question because your question did not say "how do I convert the decimal part of these times" but "how do I convert the times"

So that is why I was upset with the formula style given in response to your question, and not only that I was not only referring to your question with my comments but also to the OP where most people answered a different question.

Nothing personally directed at anybody. Just a general comment, that hey, we need to try and make everything as simple as possible.

[cj]

I interpreted the request and tried to help. I would be happy to exlain the formula if anyone wanted to know, but most people don't give a shit, they just want the result. You don't have to type anything, just paste what I wrote.

If he was requesting something as simple as A1*5, I doubt he would have had to ask in the first place. Just my two cents, sorry I somehow pissed you off.

[gl45]

CJ,
and I would more than happy to have the formula explained to me in a PM. I do appreciate nice people.
gl

[equicom]

CJ, now that I understand what you were doing for GL, I don't see any problem in that... on the other hand, GL could have worded the question a little better.

What I found more perplexing was why you gave Jeebus the answer that you did. I say this because he didn't want a formula for calculating, he wanted to know how to get Excel to display the times that he typed in so that they would be formatted correctly and still have the proper values.

Dozens of people answered, and nearly all gave some weird answer involving calculations on various cells, when the only 100 per cent correct answer was "format the cell".

I'm not being mean here, I don't want you to think I'm agitated or having a go at you, it is just I have noticed there seems to be a tendency for people on the board to jump straight to the most complicated method first without looking for a simple way to do something.

[cj]

Fair enough Equicom. I still don't think the answer to the post you mention could be done with simple formatting. I think it is just a question of us not agreeing on the poster's request, no big deal.

[equicom]

Yeah, actually it can be done with formatting, but there is a special condition to it that is not well known.

Basically, MS decided that if you put in a time such as 1:29.2 then you are recording a time and that the format for the time is HH:MM:s

Which probably makes sense if you are recording times for general business use. But if you are in the sports industry, you would want to record times differently, because you'll normally be dealing with times less than 1 hr.

To do this, you need to use a custom format "M:SS.00" which tells excel that you want the numbers to be minutes, seconds, hundreths (for tenths, you just leave off the last 0).

The only bad bit to it is you can't just type in 25.2, you now have to type in 0:25.2 but that's a lot better anyway I think. The good thing about it is that you can now use the correctly formatted column to do calculations and they will work properly if the data is entered correctly.

[cj]

This is why it doesn't hurt to have a few examples given. I know how to format cells. In this case, since the poster is entering the data by hand, I don't think he wants to enter a 0 and a : for each time. Maybe he does, maybe not, but now he has options.

[Tom]

25.2 works fine for me as is.
Cheap date.:cool:

[asH]

CJ, why did you discontinue copy and paste?

[cj]

I'm not sure what you mean. I didn't discontinue anything.

[asH]

Sorry Cj , that was for Jeebus.
Was wondering why he discontinued copy/ paste, seems far easier to copy/paste the values, set up a parse of the times so as you pasted the info into the spreadsheet everything is then automatic; 1,2,3 (or E,x,c-el )…more time interpreting the info less time manually entering values

asH