# Time Duration

#### mike2246

##### New Member
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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Enter as "HH:MM:SS.SSS"

(edit max decimal places is 3)

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

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

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

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.

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

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!

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.

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?

Replies
5
Views
417
Replies
2
Views
322
Replies
1
Views
2K
Replies
0
Views
307
Replies
0
Views
570

1,203,456
Messages
6,055,544
Members
444,795
Latest member
cjohnson333

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back