Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Help need for extracting the Date field

  1. #1

    Help need for extracting the Date field



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a column that contains the following values....in Character format...
    6/6/2011 8:00:00 AM

    I want a extract the date portion from this field and place it in a separate field with Date Format so that I can group the data by Month.

  2. #2
    =DATEVALUE(LEFT(A1,FIND(" ",A1)))

    This should do the trick.

    Jesse

  3. #3
    Need generic formula for that Input format...I do not want to input the date value into this formula....

    attached a sample file
    Attached Files Attached Files
    Last edited by komarag; 2011-08-16 at 02:26 AM.

  4. #4
    DATEVALUE is a function, converts text based dates to Excel dates.

    Jesse

  5. #5
    I understand Jesse..Please find the sample file attached.

  6. #6
    What is it that you want the data to look like for output, just the month number or name?

    Jesse

  7. #7
    I just want the date value to be extracted .....No timestamp.
    If the input field is, 6/6/2011 8:00:00 AM
    the output should be 6/6/2011

  8. #8
    What I gave outputs just the date but will be formatted based on whatever the cells are formatted as. If you wish to format it in the formula you could use:

    =TEXT(DATEVALUE(LEFT(A1,FIND(" ",A1))),"mm/dd/yyyy")

    To have it automatically format the date. If the cells are formatted to show time and date then Excel shows whole days with a time of 12:00:00AM.

    Jesse

  9. #9
    Tried it...Not working for me...

    S NoSchedule StartOutput Value108/15/2011#VALUE!209/06/2011#VALUE!309/12/2011#VALUE!409/15/2011#VALUE!508/16/2011#VALUE!

    =TEXT(DATEVALUE(LEFT(B2,FIND(" ",B2))),"mm/dd/yyyy")

    Can you use the Excel file that I gave for testing this formula?

    Thanks for your time and help.

  10. #10
    sample file.xlsx

    Try this. Your sheet doesn't have the the date stored as text, it has it stored as a date. To strip the time portion off all you need to do is drop the decimals.

    =TRUNC(A1)

    Jesse

Page 1 of 2 1 2 LastLast

Posting Permissions

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