How do I calculate time, not time of day?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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

Sloth said:
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.

DW-WD said:
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
Click to expand...
Click to expand...
 
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.
 
Back
Top