• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Taking text out of a cell

K

keithe.beyer@yahoo.com

New Member
I have a downloaded report that puts the date and time in the same cell : 2/1/12 7:54

Is there a formula I can use to seperate the two instaed of going in to each cell or any LONG workaround?
 
Date and time is a single number. The date is the INT portion, and the time is the fractional portion.


Cell A1 contains your printed value.


Format the cell as "Date" and you'll not see the time.


Cell B1 contains the same data. Format the cell as "Time" and you'll not see the date.


If you need to separate the two into date and time,


C1 = INT(A1)

D1 = A1 - INT(A1)


Note that D1 now contains that time on the "zeroeth" of January, 1900.


Also, look up "datevalue()" in help. I'm not sure if you'll need that or not, without a spreadsheet to go by.
 
Also,


http://office.microsoft.com/en-us/excel-help/convert-dates-stored-as-text-to-dates-HP001216502.aspx


That might be more what you need to do.
 
http://office.microsoft.com/en-us/excel-help/convert-dates-stored-as-text-to-dates-HP001216502.aspx


Datevalue may also be what you need.
 
Thanks for the help, but the date is the one that I need to come out as a seperate the most. You are correct it put them all in the "zeroeth" of jan 00.

Looked up datevalue, not a lot of help. How do I keep the date in tack not so much the time.
 
As DonMinter already pointed out, just date the integer portion of the whole thing:

=INT(A1)

to get the date.
 
Hi, keithe.beyer@yahoo.com!


Applying proper formats to the same formulas provided by DonMinter I arrive to this uploaded file:

http://dl.dropbox.com/u/60558749/Taking%20text%20out%20of%20a%20cell%20%28for%20keithe.beyer%40yahoo.com%20at%20chandoo.org%29.xlsx

Please check if it's what you were looking for.

Regards!
 
You can separate the date and time (if it is formatted as you show) by

=RIGHT(A1,LEN(A1)-FIND(" ",A1)) That leaves only the time

=LEFT(A1,LEN(A1)-FIND(" ",A1)) That leaves only the date

Put those values into cells formatted as "Time" and "Date"


You can put your text (if it is, indeed, text) in cell A1, and those formulas in any other cell. OR you can just put


=DATEVALUE(A1) into a cell formatted as Date

=DATEVALUE(A1) into a cell formatted as Time
 
Thanks yall, but I am trying to use the data in a pivot table and keeps ALL the orginal cell data instead of condensing them like in the filter using the formula provided by DonMinter. That works fine on the spreadsheet and filter, but not for the pivot table. Sorry should have said that in the begining.
 
=DATEVALUE(A1)


Put your text in A1 (or change A1 to whatever cell your text is in). You now have the Date and the time.


Excel does NOT have a real "Time". It does NOT have a real "Date." It has, instead, Date.Time. That's a single number with Date the number of days since 0 Jan 1900, and the time a fractional number representing milliseconds (if I remember correctly). You cannot, ever, have a date without a time or a time without a date in Excel. You can make the time "Zero" and you can make the Date "Zero," but that doesn't mean you don't have one. It means the time is exactly midnight in the "morning" and the date is 0 January 1900.
 
Sorry... in my prior post, to get time, you use the "=right" formula to get the time by itself, then use "=timevalue()" to pull out the time as a time (rather than a number).


=TIMEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1))) That leaves only the time as a number

=DATEVALUE(LEFT(A1,LEN(A1)-FIND(" ",A1))) That leaves only the date as a number


That's all you would need to do to break down your date + time text to Excel Date and Time numbers.
 
From my first response, Fred:


'Format the cell as "Date" and you'll not see the time.


Cell B1 contains the same data. Format the cell as "Time" and you'll not see the date.'


At this point, I'm not sure what the OP is still lacking.
 
Well as I said that I am trying to use the data in a pivot table if you just change the format it just changes the view. I need it as just the date in order to work the data. C1=INT(A1) that Don provided, also does not work for the pivot table resources as it keeps the original data. I alos tried the TIMEVALUE / DATEVALUE process to seperate, but keeps coming up as #VALUE
 
Change your PivotTable. You'll need to remove the field that refers to original data, and add the field that refers to column with helper cells that show just the Data.
 
If the example you posted

2/1/12 7:54

is TEXT, then you need to use


=DATEVALUE(LEFT(A1,LEN(A1)-FIND(" ",A1))) That leaves only the date as a number.


If it is an excel time/date value, then you need to use

INT(A1)


Both of these formulas expect the example you posted to be in A1. Both of these formulas will leave only the Date portion of the data you posted. If you post some sample data, we might be able to help you a bit more.


Don
 
I'll try it, but I found a little work around, if I copy the data from the C1=INT(A1) that Don provided into another column and paste special Format Values, the pivot table excepts that.


THANKS YALL !!!!!!!!!!!!!!!!!! Great site!
 
You, too, Luke. Here in Dubai, I'm already one day through my weekend. It starts on Friday, not Saturday. Thanks for the help here. I was at my wits' end trying to figure out why my explanations weren't working. I overlooked the possibility that Keithe might not be looking in the cell with the formula when he said it "keeps the original data."
 
You must log in or register to reply here.
Back
Top