Excel Question #46

Alex · 06-27-2014, 05:01 PM

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.

06-27-2014, 05:01 PM	#34
Alex . Join Date: Feb 2005 Posts: 13,354	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.
	Submit to Quotes