Share via

How do I remove the time section of a date format

Anonymous
2011-03-29T21:06:34+00:00

I have a huge amount of data where the Date columns contain dates in the format 3/3/2010  11:00:00 AM. But it is not possible to remove the time section,11:00:00 AM, using Replace or Format as Date. Is there any other way to get rid of the time section from the date.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2011-03-30T02:22:03+00:00

    Hi,

    Try this.

    1. Select the data and press Ctrl+H (Find and Replace)
    2. In the find box, enter space* i.e. space and then *
    3. Leave the replace box blank
    4. The entries will now carry a 00:00 time stamp
    5. Press Ctrl+1 (Format cells)
    6. In Custom format, enter dd/mm/yyyy

    Hope this helps.

    200+ people found this answer helpful.
    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-03-29T21:09:46+00:00

    You can use a formula in a helper column like:

    =INT(A2) copied down

    Then copy and paste special > Values this new column over the original (and, if necessary format as date).. then delete the formula column.

    10+ people found this answer helpful.
    0 comments
  2. Anonymous
    2011-03-29T21:32:16+00:00

    I have a huge amount of data where the Date columns contain dates in the format 3/3/2010  11:00:00 AM. But it is not possible to remove the time section,11:00:00 AM, using Replace or Format as Date. Is there any other way to get rid of the time section from the date.

    If your data is really a date with time in Excel time format it should be possible to just use a Date format to only show the date part.

    You can find out if the data in cell A1 is in time format by testing with =ISNUMBER(A1)

    If the result is TRUE, then a simple format should help.

    But as you say you have tested that I assume that the result of this test is FALSE.

    That means that the data is in text format (and you can't use it for arithmetics).

    If that is the case you can filter out the date part of the text with this formula:

    =LEFT(A1,FIND(" ",A1)-1)

    Hope this helps / Lars-Åke

    7 people found this answer helpful.
    0 comments
  3. OssieMac 48,001 Reputation points Volunteer Moderator
    2011-03-29T22:36:24+00:00

    It appears that the dates are in text format therefore need to convert.

    Select all of the columns with date/time and use number format to set the required date format. (Nothing will appear to change at this stage)

    In a blank cell on the worksheet enter 1.

    Copy the cell with the 1 and then select all of the dates (don't select entire columns; just the cells with the dates.)

    Select Paste Special and select the Multiply button and OK.

    All of the cells should now be in the correct date format.

    The multiply by 1 forces the change from text to numeric without changing the value.

    6 people found this answer helpful.
    0 comments
  4. Anonymous
    2011-03-29T21:28:31+00:00

    I have over 50 columns. And I don't want to be doing this everyday when I download the data. I am looking for something easier and simplier.

    3 people found this answer helpful.
    0 comments