[ms office - 2010] Excel - How to remove date time formatted cell?

[dany2010]

Hi,

 

I would like a cell to just contain a time so when you type in 14 it will show as 14:00 bt even with the cell formatted as time it shows the date and puts the 14 in the date and sets the time to 00:00.

 

Is it possible to remove this?

 

Thanks

[Pico]

Short Answer

 

As far as I can see, the answer is no, unfortunately. You have to enter the colon as well.

 

Long Answer

 

Excel handles dates and times as numbers, with 1 representing the 1st of January 1900, 2 representing the 2nd of January 1900 etc., with times represented by fractions, e.g. 1.2 represents 4:48 am on 01/01/1900. Dates (i.e. whole numbers) are dates as at midnight exactly. This underlies Excel's handling of date and time calculations.

 

Although you've entered 14 into the cell and instructed Excel to format it as a time, this is displayed as 14/01/1900 00:00:00 (or similar). If you want Excel to display just the time portion and ignore the date, you have to enter it as 14:00. Without the colon portion, Excel will treat your number as a date and time combination. Entering the colon tells Excel it's just the time portion you've entered.

Edited February 25, 2013 by Pico
Typo

[Madison43]

hello,

 

i often have the most annoying problem with excel. it automatically decides how to format specific data.

here's one example. i've imported a text file with some timing information in minutes:seconds. every time excel automatically makes it hours:minutes. if i go and format the information to minutes:seconds, the data is then show as incorrect. here's another example. the data is 28:17 (28 minutes, 17 seconds). well, when i import it, the data is now conveted to "01-01-1900 04:17:00". even when i try to copy the format of another cell, it then changes the data to 04:17:00. argh!!! why on earth does excel change 28:17 to a date and a time in 1900?another classic one is if you type 05-09, excel will change it to 05-sep. as if the information is a date in -- 05 september 2003. it's so irritating that it does this.what causes this to happen? why does it automatically decide how toformat the information? whatever it is i would like to turn it off so that excel never formats any data until i tell it to.Do you know about SharePoint?If you know so help me.

[CESIL]

Format the column where the date or time figures will be imported as "text" I believe this will force Excel to treat the data as text until you tell it otherwise.

 

Can you also format the source data to the "00:00:00" format? ie add the leading zeroes to indicate no hours?

[Pico]

Microsoft's Customer Experience Improvement Program (or whatever they did back in the day) is probably where these features originated from. The data from that program probably indicates that when someone types in 05-09 they want to use it as a date. If you're typing data directly into Excel, you can force Excel to display what you type as text by adding an apostrophe, as in '05-09. The downside is that you can't perform numeric calculations on such textual data.

 

The reason why 28:17 is rendered as 01-01-1900 04:17 derives from what I said above, once you take into account the fact that there are 24 hours in a day. 28:17 means 28 hours and 17 minutes, so, as CESIL says, you could have 00:28:17 in your CSV to ensure that Excel reads the data correctly. If you want to do calculations on your times, then this is probably the best solution.

 

The way Excel is set up makes it look like there is a fairly clean separation between the data stored in a cell and the way it is presented (i.e. formatted), but there are evidently limitations to this. I guess Microsoft made the reasonable assumption that there are times when the semantics of some piece of data has been indicated at the point of data entry, e.g. 28:17:00 means 28 hours, 17 minutes and 0 seconds, and that in most cases it will save people time if Excel can recognise that fact.

[Madison43]

Thank you all for advice me.

[pcstru]

If you are entering data by keyboard and want excel to take you literally, use a ' (single quote) before the data. That will force it to text (it won't try and do anything clever with it).

[rosevaldez]

We have an option as custom when you try to format cell. Select the category as custom and type as h:mm or h:mm AM/PM.

[Madison43]

Hi all

[Tamara123]

Hi guys, I found a very helpful site for excel problems. The workshop with video shows very well how to set the date. Excel: Year/ Month/ Day- how does it work?