Time Duration

[mike2246]

I'm making a Training Excel sheet that will track various categories, Mile/Half Mile/40yrd Dash are on there. I'll have 4 testing times (initial/2nd test/3rd test/final)
I need a way where I can input Time Duration so if it's 4:35 (4min 35sec) it will show as 4:35 or even 4.35. this way I can calculate % Difference and see trending on graphs

 

[SpillerBD]

Enter as "HH:MM:SS.SSS"

(edit max decimal places is 3)

 

[gaz_chops]

You can simply enter 4:35 and excel will recognise it's 4min 35 secs.

 

[mike2246]

Enter as "HH:MM:SS.SSS"

(edit max decimal places is 3)

Do I do this in Format Cell? if so I tried and it didn't work, I chose custom entered what you have and I get the following
04:35:00.00 and in the formula bar at the top it says 4:35:00 am

 

[gaz_chops]

I don't get what you're trying to do? Why not just enter 4:35?

 

[SpillerBD]

04:35:00.00 and in the formula bar at the top it says 4:35:00 am

understand where the origin is in Excel's time/date keeping. The first 4 hours are in the AM. In Excel Time began on Jan 1, 1900 (unless you're on the 1904 system.)
If you choose the formatting "[H]:mm:ss.sss" you won't get the AM/PM notation, which is not a part of the value but a part of the formatting.
Caution, if you have math that will yield negative values, you'll have to reconsider your strategy.

 

[SpillerBD]

I don't get what you're trying to do? Why not just enter 4:35?

Because that would be 4 hours 35 minutes.
should be "0:4:35" for zero hours 4 minutes and 35 seconds (from Excel's beginning of time.)

 

[gaz_chops]

Because that would be 4 hours 35 minutes.
should be "0:4:35" for zero hours 4 minutes and 35 seconds (from Excel's beginning of time.)

So you just enter 0:04:35, excel will auto format as time, i don't understand why it is necessary to format!

 

[SpillerBD]

Maybe he'll total up the total time of the participants. Formatting will be essential in displaying the correct total if that total were to exceed 24 hours.
Likewise in any calculation it is important to maintain the correct unit of measure. So recording Minutes as minutes instead of as hours is a best practice.

 

[gaz_chops]

Maybe he'll total up the total time of the participants. Formatting will be essential in displaying the correct total if that total were to exceed 24 hours.
Likewise in any calculation it is important to maintain the correct unit of measure. So recording Minutes as minutes instead of as hours is a best practice.

So you change the format, you then enter 4:35, what answer do you get?