Running Workbook with Pace

[Hydestone]

I've been tracking my run information in an Excel workbook. The basic information I want to record is distance in miles, duration of run, and average pace.

I'd like to enter the distance (miles) and duration (minutes) and have it calculate my pace.

Additionally, I'd like to tally up duration and distances for the week and give me average pace per mile for the weekly data.

The problem I am having is that whenever I enter the duration information, Excel changes it to a date format. I've tried different MM:SS combinations, but nothing seems to work properly.

Any suggestions on how to enter time into the spreadsheet without it being converted to a date/time?

I assume I could then divide that by mileage to get pace.

Thanks

 

[JoeMo]

Not sure I understand your problem. In the example below, C2 and D2 are both formatted as "mm:ss" - the pace is 7 minutes and 5 seconds per mile. Is this what you want?

Excel Workbook
	A	B	C	D
1	Date	Distance (mi)	Duration (mm:ss)	Pace (Mins/Mile)
2	5/8/2017	6.35	44:58	07:05
Sheet1

 

[Hydestone]

For some reason, when I format the cells as mm:ss (from the format cells > custom menu) I get a different result. When I enter 45:16, the result in the cell is 16:00. The formula bar for the cell displays 1/1/1900 9:16:00 PM.

 

[JoeMo]

For some reason, when I format the cells as mm:ss (from the format cells > custom menu) I get a different result. When I enter 45:16, the result in the cell is 16:00. The formula bar for the cell displays 1/1/1900 9:16:00 PM.

Sorry, forgot to tell you to enter the time in hh:mm:ss format. In your example that's 00:45:16. The formula bar should show 12:45:16 AM (i.e. 45 minutes and 16 seconds past the stroke of midnight - effectively 0 hour.

 

[Hydestone]

Ah, that does it! So the format of the cells should be mm:ss, but entered in as hh:mm:ss. For the weekly total, I should probably format to hh:mm:ss.

Thanks Joe

 

[JoeMo]

Ah, that does it! So the format of the cells should be mm:ss, but entered in as hh:mm:ss. For the weekly total, I should probably format to hh:mm:ss.

Thanks Joe

You are welcome - thanks for the reply.