Excel Question #46

[Betty]

Originally Posted by Moonliner

You probably already figured this out, but you need to make the same change to cells G13 and G21

Thanks.

[Moonliner]

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]

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):

1. Enter this function in column B: =LEFT(A2,11) (the date portion is always 11 characters)
2. Enter this function in column C: =DATEVALUE(B2)
3. 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]

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?

[Alex]

This will result in losing the time portion of the cell but doesn't require any formulas and would be a very simple macro to record.

If so, then you can do this and not mess with any formulas. Assumes the timestamp is in column A. Easy macro to record if you want.

1. Select Column A
2. Set Number format of Column A to whatever format you want to see the date expressed in. For example purposes pick 3/14/01.
3. With Column A selected (to avoid collateral damage in other columns) open the Search and Replace dialog (Ctrl-H).
4. In the "Find what" box enter " *" without the quotes (that is: <space>*). This will tell Excel to find the first instance of a space in the cell and then replace that space and everything to the right of it with what comes next.
5. In the "Replace with" box enter nothing at all.
6. Click "Replace all" button.

This will take a cell that says

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

and essentially truncate the value at the first space. This truncated form is one that the date formatting recognizes and so you'll now see

6/26/14

You can then sort, pivot, use in formulas, etc., however you want.

[Alex]

Oh, and if you want to keep the time part of the time stamp around, before you do the above, copy Column A into a blank column (let's say Column B).

Do the above to Column A. Then in Column B things are a bit trickier to get rid of the date (because for some stupid reason when you want to trim tot he left the expression match starts from the right so just finding spaces will match to the one before PDT).

But these could clear out the date part if you need:

If all timestamps are from 1/1/2000 and later:

Find what: "*20?? " without quotes: *20??<space>

This essentially says "find the pattern 2 0 any character any character space and then trim that pattern and everything to the left of it."

If all timestamps are from the 1900s and 2000s:

Find what: "*20?? " without quotes: *20??<space>

then repeat with:

Find what: "*19?? " without quotes: *19??<space>

[Alex]

Kevy better be dead to explain him not coming back to thank us vociferously for being such Excel badasses.

Or just let us know that it filled his need.

[RStar]

Or he just saw boobs and forgot...

[Kevy Baby]

Oh Geez: I'm not sure how, but I missed your responses.

I will check these out tomorrow.

And thank you for your responses!!!

[RStar]

Well it's about time!

Good luck!