Help need for extracting the Date field

komarag

New member
Joined
Aug 9, 2011
Messages
26
Reaction score
0
Points
0
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.
 
=DATEVALUE(LEFT(A1,FIND(" ",A1)))

This should do the trick.

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

attached a sample file
 

Attachments

  • sample file.xlsx
    8.1 KB · Views: 15
Last edited:
DATEVALUE is a function, converts text based dates to Excel dates.

Jesse
 
I understand Jesse..Please find the sample file attached.
 
What is it that you want the data to look like for output, just the month number or name?

Jesse
 
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
 
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
 
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.
 
View attachment 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
 
Thanks for the tip and formula.

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