Excel Question #46

Moonliner · 10-05-2012, 10:24 AM

I was assigned a "high priority" task today. A "You are to work on nothing but this for the new few days" thing.

The task is to match up some data in a couple of overly large excel spreadsheets.

So I imported the data into SQL Server and created a couple of cross table queries to pull the needed data and drop it back into excel. Total time: ~45min.

So my question is, what's good on Netflix? I have a few days to kill.....

Kevy Baby · 06-27-2014, 12:08 PM

I have a spreadsheet that can only be downloaded as a tab delimited file. One of the fields is a date/time field that comes in as text in this format:

26-Jun-2014 10:11:13 AM PDT

Obviously I cannot just format it as a date. The shortest way I have found to get this converted to a date is (assuming the source data is in column A):

Enter this function in column B: =LEFT(A2,11) (the date portion is always 11 characters)
Enter this function in column C: =DATEVALUE(B2)
Copy then Paste Value of Column C

Yes, I will combine the two functions into one: I left them separate to make the example easier to follow. Also, there are several hundred rows of data and the formula will need to be filled down.

So
1. Is there a simpler way to do this in Excel? I need to be able to sort/group/etc. the data by dates and other data in the spreadsheet (daily summary of debits and credits by company name in a monthly report)
2. Having never worked in Macros (but always willing to learn something new) how difficult is this to script so that the CSV file can be automatically processed once per month? I want to be able to hand off to our accounting department to handle (I could manually do this every month myself in probably less time than I would spend writing and testing said macro, but I would rather automate and give to someone else to handle).

And I cannot change the report as it is provided: I gotta work with what I have.

Moonliner · 06-27-2014, 12:23 PM

The basic formula looks good: =DATEVALUE(LEFT(A1,11))

I would read Tab delimited data into "sheet2" and then use the formula on "sheet1" to format and display it the way the users need.

Does that help at all?

10-05-2012, 10:24 AM	#1
Moonliner 8/30/14 - Disneyland -10k or Bust. Join Date: Jan 2005 Posts: 9,022	I was assigned a "high priority" task today. A "You are to work on nothing but this for the new few days" thing. The task is to match up some data in a couple of overly large excel spreadsheets. So I imported the data into SQL Server and created a couple of cross table queries to pull the needed data and drop it back into excel. Total time: ~45min. So my question is, what's good on Netflix? I have a few days to kill..... __________________ - Taking it one step at a time.
	Submit to Quotes

06-27-2014, 12:08 PM	#2
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	I have a spreadsheet that can only be downloaded as a tab delimited file. One of the fields is a date/time field that comes in as text in this format: 26-Jun-2014 10:11:13 AM PDT Obviously I cannot just format it as a date. The shortest way I have found to get this converted to a date is (assuming the source data is in column A): Enter this function in column B: =LEFT(A2,11) (the date portion is always 11 characters) Enter this function in column C: =DATEVALUE(B2) Copy then Paste Value of Column C Yes, I will combine the two functions into one: I left them separate to make the example easier to follow. Also, there are several hundred rows of data and the formula will need to be filled down. So 1. Is there a simpler way to do this in Excel? I need to be able to sort/group/etc. the data by dates and other data in the spreadsheet (daily summary of debits and credits by company name in a monthly report) 2. Having never worked in Macros (but always willing to learn something new) how difficult is this to script so that the CSV file can be automatically processed once per month? I want to be able to hand off to our accounting department to handle (I could manually do this every month myself in probably less time than I would spend writing and testing said macro, but I would rather automate and give to someone else to handle). And I cannot change the report as it is provided: I gotta work with what I have. __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln
	Submit to Quotes

06-27-2014, 12:23 PM	#3
Moonliner 8/30/14 - Disneyland -10k or Bust. Join Date: Jan 2005 Posts: 9,022	The basic formula looks good: =DATEVALUE(LEFT(A1,11)) I would read Tab delimited data into "sheet2" and then use the formula on "sheet1" to format and display it the way the users need. Does that help at all? __________________ - Taking it one step at a time.
	Submit to Quotes