How can I make Excel format cells?

Microsoft Excel 2007
November 8, 2009 at 20:25:33
Specs: Windows Vista
I have an extremely simple Excel spreadsheet that I use to keep track of my running. It's columns are: Date, time to run a mile, time to run 2 miles, distance covered in 30 minutes, total distance, and then comments.

It seems so simple, but I just can't make Excel work.

1. How can I enter times? I've scoured the cell formatting options and I just can not find any hours:minutes:seconds option. I've settled with minutes:seconds.hundredths, which is awkward to type in. With the jillions of formats available, why isn't there one for a normal amount of time? I don't track hundredths of a second, for Gods sake.

2: Every single time I enter a new workout's info, I have to reformat the columns to the right of the awkwardly displayed times. It doesn't matter that I've previously selected the entire column for total distance and told it to use "general" formatting. Every time, the distance I type in (like '3.0') gets mangled into a nonsensical minutes:seconds.hundredths format. Why won't it display the number with general formatting like I try to tell it to?

Thanks for any help!

See More: How can I make Excel format cells?

Report •

November 8, 2009 at 21:02:14
I'm looking at Excel 2007 right now and the forth option down under the "Time" category is "13:30:55" which is hours:minutes:seconds. There are also several other ones in there like that. But should none of those work for you, there is also a "Custom" category where you can set your own format.

I don't know why you are having the second problem, but you should make sure you are in fact selecting the whole column (by clicking the column header) before applying the formatting. You might also try using "Number" instead of general since distances are numbers.

-Ryan Adams

Free Computer Tips and more:
Paid Tech Support: Black Diamond

Report •

November 9, 2009 at 05:35:48

Excel tries to format cells according to what you type and also tries to match existing formating.

To stop it formatting what it thinks you want, use a specific format, not General.

Select the columns that will contain times. Select the Custom format and enter "hh:mm", click OK

Select the columns that will contain distances. Select the Number format and select the number of decimal places you want to show, click OK

Your formats should now stay put.

In the time columns the ":" is not required, you can use a space or "-" or whatever suits you.

You could enter
h" hour(s) and "mm" minutes"
The text to display is surrounded by double quotes
Enter 02:15 in the cell and it displays as
2 hour(s) and 15 minutes

A custom format [mm] will display the time in minutes only e.g. 1 hour 30 minutes is displayed as 90

Times can be subtracted from each other to give the difference for use in your calculations


Report •

November 9, 2009 at 12:08:29
Sweet! Thank you so much! I have no idea how in the world I missed the time format option. I must have seen the "AM" that pops up in the data entry box and assumed that it was formatting wrong, but the AM pops up regardless. The spreadsheet shows the correct info.

Sure enough, once I told it that i wanted a number instead of general, it remembered the setting. I can live with unnecessary decimals on the distance measures as long as I don;t have to reset it each time.

Thanks a ton for the help!

Report •

Related Solutions

November 9, 2009 at 12:20:00

Re: unnecessary decimals on the distance measures

Format the cells as numbers - and on the same Tab there is a box with scroll buttons, to select the number of decimal places displayed.


Report •

Ask Question