Hi all,
I have inherited a data set with some important historical data that I need to work with. The issue I am having is that the date and time is saved as one cell with just the number 200810151200 for 2008 october 15 1200. It is a huge data set with values generated each 15 minutes all year. I would like to clean this up into a column with the date (formatted day/month/year) and a second column with the time. I have tried formatting the cell as a date with various date formats to no avail. is there any quick way to extract the values for columns or do I have to go through and type everything out?
Hi,
Try this:
For the date: =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))
For the time: =TIME(MID(A1,9,2),RIGHT(A1,2),0)
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Works perfectly. Thank you for the prompt and excellent response.
Similar question. Now the date is save as 12/1/2007 12:00:00 AM formatted as date and time cell. If i change the format to general number it is 37955. What is the best way to separate the column into one column for date and one for time?
If A1 contains a serial date/time value then:
=INT(A1) will give you the date
=MOD(A1,1) will give you the time
Format the cells as appropriate.
Dom
Last edited by Domski; 04-11-2011 at 09:12 AM.
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks