cannot delete certain formats

[las]

I wonder if you can help. I am doing a spreadsheet on excel 2007 which i need hours:minutes and i have changed the worksheet to accomodate this. On certain rows, for example i am typing in 31:00 and it is automatically changing it to 07:00 and in the formula bar it states 01/01/1900 07:00:00 and i cannot change this no matter what i do. I have even closed without saving and tried a new worksheet but it is still doing the same. Can anyone help me please (this has happened both on my work pc and home) Thank you

[SpywareDr]

Try this:

1. Right-click the cell with the "31:00" in it and select "Format Cells...".
2. Click the "Number" tab.
3. On the left, under "Category:", scroll down and select "Custom".
4. On the right, in the "Type:" box, type in these exact characters:
   [hh]:mm
   then click "OK".

That's it. You should now only see a "31:00" in your cell.

(Note that you can highlight multiple cells and format them all with the same format).

For what I think you're doing, you should be able to ignore the "01/01/1900 07:00:00 AM" in the formula bar. Here's an explanation from Microsoft Excel Help > About dates and date systems:

Microsoft Excel stores dates as sequential numbers which are called serial values. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day.

Because dates and times are values, they can be added, subtracted, and included in other calculations. You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format.

Because the rules that govern the way that any calculation program interprets dates are complex, you should be as specific as possible about dates whenever you enter them. This will produce the highest level of accuracy in your date calculations.

[las]

Thank you so much. It has worked. What a fantastic site, my first visit and something that has taken me 2 days to try and work out has been solved. Thank you

[SpywareDr]

You're more than welcome. Thank you for posting back.