Closed Thread
Results 1 to 9 of 9

Remove Time on Date/Time Stamp

  1. #1
    Registered User
    Join Date
    09-15-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Remove Time on Date/Time Stamp

    The current date/time stamp is below:

    9/2/2009 8:59PM

    I need to keep the date but remove the time stamp on the data. I have been using the formula below:

    =MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1)

    But now it is giving me a !value error. Can anyone help? THanks!

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Remove Time on Date/Time Stamp

    If it is indeed an Excel date (rather than text), =int(a1) is all you need.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Remove Time on Date/Time Stamp

    Hi,

    shg's approach is surely the quickest and most elegant. But you're not far off from a solution with your approach. Instead of concatenating with the & sign, you can use the date function like this:

    =DATE(YEAR(A1),MONTH(A1),DAY(A1))

    then format the cell to display the date the way you like it

  4. #4
    Registered User
    Join Date
    09-15-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Remove Time on Date/Time Stamp

    I am getting a #VALUE!


    Is it because it's a text date?


  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Remove Time on Date/Time Stamp

    Is it because it's a text date?
    Probably. Try =YEAR(A1) on its own. If that returns #Value, you're dealing with a text, not a date. Now you have two options:

    extract the date from the text as a text
    =LEFT(A1,LEN(A1)-FIND(" ",A1)+2)

    The result will still be text.

    convert the text into dates
    If the dates are written exactly as you did above, you only need to add a space before the PM and then format the cell as date. After that, you can use the function shg supplied.

    try this: highlight the whole range of cells with this kind of date stamp,
    select Edit - Replace
    search for "PM" and replace with " PM" (not the space!, don't add the " marks!!)
    hit Replace all
    repeat for AM

    now format your cells as dates and play on.....

  6. #6
    Registered User
    Join Date
    10-06-2011
    Location
    Colchester, England
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Remove Time on Date/Time Stamp

    All I did was format the cells into a dd/mm/yyyy and it automatically removed the time.

    Lots easier than using formulas!


  7. #7
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Remove Time on Date/Time Stamp

    Tracyd32,

    please read what the thread is about before posting wrong solutions.
    Formatting only changes how the cell looks, not the underlying value.

    This being said, I doubt if the OP has waited two years for your input.

  8. #8
    Forum Contributor
    Join Date
    09-29-2011
    Location
    Cape Town
    MS-Off Ver
    Excel 2010
    Posts
    107

    Re: Remove Time on Date/Time Stamp

    Hi I had a similar problem and this is my solution:

    1) Select Text to Column button on the data tab.
    2) Select Fixed Width then next
    3) Then hit next again
    4) Under column data format select date and choose the format.
    Then hit finish.

    The time will be separated from the date and placed in the adjacent column leaving the date.
    I'm assuming the reason INT didn't work was because the data was text. The text is now transformed into a date. Very useful!

  9. #9
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Remove Time on Date/Time Stamp

    2 year old thread - Cosed

Closed Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1