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.
 

Jesse

New member
Joined
Mar 22, 2011
Messages
51
Reaction score
0
Points
0
=DATEVALUE(LEFT(A1,FIND(" ",A1)))

This should do the trick.

Jesse
 

komarag

New member
Joined
Aug 9, 2011
Messages
26
Reaction score
0
Points
0
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:

Jesse

New member
Joined
Mar 22, 2011
Messages
51
Reaction score
0
Points
0
DATEVALUE is a function, converts text based dates to Excel dates.

Jesse
 

komarag

New member
Joined
Aug 9, 2011
Messages
26
Reaction score
0
Points
0
I understand Jesse..Please find the sample file attached.
 

Jesse

New member
Joined
Mar 22, 2011
Messages
51
Reaction score
0
Points
0
What is it that you want the data to look like for output, just the month number or name?

Jesse
 

komarag

New member
Joined
Aug 9, 2011
Messages
26
Reaction score
0
Points
0
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

New member
Joined
Mar 22, 2011
Messages
51
Reaction score
0
Points
0
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

New member
Joined
Aug 9, 2011
Messages
26
Reaction score
0
Points
0
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

New member
Joined
Mar 22, 2011
Messages
51
Reaction score
0
Points
0
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
 

komarag

New member
Joined
Aug 9, 2011
Messages
26
Reaction score
0
Points
0
Thanks for the tip and formula.

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