+ Post New Thread
Results 1 to 10 of 10
Office Software Thread, Excel - How to remove date time formatted cell? in Technical; Hi, I would like a cell to just contain a time so when you type in 14 it will show ...
  1. #1

    Join Date
    Nov 2011
    Posts
    628
    Thank Post
    87
    Thanked 21 Times in 19 Posts
    Rep Power
    11

    Excel - How to remove date time formatted cell?

    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

  2. #2

    Join Date
    Oct 2010
    Posts
    24
    Thank Post
    6
    Thanked 1 Time in 1 Post
    Blog Entries
    2
    Rep Power
    43
    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.
    Last edited by Pico; 25th February 2013 at 04:59 PM. Reason: Typo

  3. #3

    Join Date
    Mar 2013
    Location
    uk
    Posts
    4
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Advice need

    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.

  4. #4

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,405
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    169
    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?

  5. #5

    Join Date
    Oct 2010
    Posts
    24
    Thank Post
    6
    Thanked 1 Time in 1 Post
    Blog Entries
    2
    Rep Power
    43
    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.

  6. #6

    Join Date
    Mar 2013
    Location
    uk
    Posts
    4
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Thank you all for advice me.

  7. #7


    Join Date
    May 2009
    Posts
    3,394
    Thank Post
    301
    Thanked 917 Times in 684 Posts
    Rep Power
    346
    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).

  8. #8

    Join Date
    Apr 2013
    Location
    Scottsdale, AZ
    Posts
    3
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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.

  9. #9

    Join Date
    Mar 2013
    Location
    uk
    Posts
    4
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Hi all

  10. #10

    Join Date
    Mar 2014
    Location
    USA
    Posts
    1
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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?



SHARE:
+ Post New Thread

Similar Threads

  1. How to remove .exe from mailboxes
    By mmoseley in forum How do you do....it?
    Replies: 1
    Last Post: 28th November 2008, 03:02 PM
  2. Acer Travelmate 270 CD Drive - How to remove!
    By danielson81 in forum Hardware
    Replies: 4
    Last Post: 5th November 2008, 05:03 PM
  3. How to remove wallpaper shown on boot?
    By sparkeh in forum Windows
    Replies: 2
    Last Post: 17th September 2008, 12:56 PM
  4. How to remove a Certificate from CA
    By PRicho in forum Windows
    Replies: 2
    Last Post: 22nd July 2008, 12:29 PM
  5. Windows XP SP3 change to Date/Time format
    By itwasntme in forum Windows
    Replies: 7
    Last Post: 11th July 2008, 02:18 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •