Help need for extracting the Date field

[komarag]

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.

[Jesse]

=DATEVALUE(LEFT(A1,FIND(" ",A1)))

This should do the trick.

Jesse

[komarag]

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

attached a sample file

[Jesse]

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

Jesse

[komarag]

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

[Jesse]

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

Jesse

[komarag]

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

[Jesse]

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

[komarag]

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.

[Jesse]

204

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

[komarag]

Thanks for the tip and formula.

Appreciate your time today in helping this one out.....