How do I calculate time, not time of day?

[Guest]

I am a swimming coach and I'm trying to set up and Excel spreadsheet to help
me create practices.

A sample set is 10 x 200 on 2:45. I want to be able to calculate how long
this set will take. Knowing that each 200 takes 2 minutes and 45 seconds, I
should be able to multiply the time by the number of repeats, 10, and have a
result of 26:30 (26 minutes and 30 seconds). How can I show this in Excel?

I also want to be able to add the time of sets together to show how long an
entire practice will take. So if one set takes 25:15, another takes 26:30
and a final one takes 42:25, my cumulative time should be 1:34:10 (1 hour, 34
minutes and 10 seconds). I can't get Excel to show and calculate time other
than time of day.

Please help

Dave

 

[Guest]

Time of Day, and elapsed time is treated the same in Excel.

For your first example enter
0:2:45
in cell A1. It will change to 0:02:35, and when you click on it you will
see it has the value 12:02:35 AM. Change the format to show only minutes and
seconds by using this custom number format
[m]:ss
it will now look like 2:35. In cell A2 enter the formula
=A1*10
it should result in 25:50 (if not change the format to [m]:ss like A1). The
value of A2 is actually 12:25:50 AM (technically it is the number
0.517939814814815, but that is a different discussion), but you don't need to
worry about that. Like I said elapsed time is essentially the same thing as
time of day.

For your second example enter the following values in A1, A2 and A3
0:25:15
0:26:30
0:42:25
and enter the following in A4
=SUM(A1:A3)
as long as you use the following custom number format, you will get the
results you want
[m]:ss

Just remember when you enter elapsed time you always have to include hours
even if they are zero.

 

[Guest]

sorry some slight mistakes in the first example, that's what I get for rushing.

0:2:45 * 10 = 27:30 = 12:27:30 AM = 0.0190972222222222

Time of Day, and elapsed time is treated the same in Excel.

For your first example enter
0:2:45
in cell A1. It will change to 0:02:35, and when you click on it you will
see it has the value 12:02:35 AM. Change the format to show only minutes and
seconds by using this custom number format
[m]:ss
it will now look like 2:35. In cell A2 enter the formula
=A1*10
it should result in 25:50 (if not change the format to [m]:ss like A1). The
value of A2 is actually 12:25:50 AM (technically it is the number
0.517939814814815, but that is a different discussion), but you don't need to
worry about that. Like I said elapsed time is essentially the same thing as
time of day.

For your second example enter the following values in A1, A2 and A3
0:25:15
0:26:30
0:42:25
and enter the following in A4
=SUM(A1:A3)
as long as you use the following custom number format, you will get the
results you want
[m]:ss

Just remember when you enter elapsed time you always have to include hours
even if they are zero.

I am a swimming coach and I'm trying to set up and Excel spreadsheet to help
me create practices.

A sample set is 10 x 200 on 2:45. I want to be able to calculate how long
this set will take. Knowing that each 200 takes 2 minutes and 45 seconds, I
should be able to multiply the time by the number of repeats, 10, and have a
result of 26:30 (26 minutes and 30 seconds). How can I show this in Excel?

I also want to be able to add the time of sets together to show how long an
entire practice will take. So if one set takes 25:15, another takes 26:30
and a final one takes 42:25, my cumulative time should be 1:34:10 (1 hour, 34
minutes and 10 seconds). I can't get Excel to show and calculate time other
than time of day.

Please help

Dave

 

[Guest]

I don't know of any way to do this without first converting the time to a
decimal number first, i.e. 2 minutes and 45 seconds equals 2.75 minutes.

If you have a time value in A1, then try this in B1.

=minute(a1) + second(a1)/60

Once you've got that value in B1, you can add, multiply, or whatever you need.