Thread: Help with formatting cells

Hi Kimmyh3, welcome to the forum.

One thought is that the dates from your database are actually coming into Excel as Text and not as actual dates. Are they aligned to the left of the cell?

Try this:
Select your range of dates, then in the Cell Formatting screen choose a Date format.
With the dates still selected, click the Data tab, then Text-to-Columns and click Finish.

Hopefully that helps. If not, try uploading a sample workbook with any private data removed - we would likely only need to see the column with the dates in it.

Hi Kimmy,

Your data contains extra spacing and differently formatted "dates". Try this:

Select column B
Right-click any cell in column B and choose "Format Cells..."
In the Format Cells dialog, select Custom from the category list; then beneath 'Type', enter: mm/dd/yyyy
Click OK

In B1 put the following formula:

=IF(ISNUMBER(FIND("-",A1)),TRIM(MID(A1,2,LEN(A1))),A1)

After doing that, hover over the lower right corner of cell B1 until the cursor becomes a + sign. Once it's a + sign, double-click and it will automatically fill that formula down to the last used row (2291) in the adjacent column (A).

Select Column B, right-click selection and choose Copy
Right-click selection again and choose PasteSpecial, then choose Values and click OK
Press ESC

Select Column B, then on the Data ribbon click 'Text to Columns' and then click Finish when the Text To Columns dialog displays.

You can then delete column A's "fake" dates.

Hopefully you can follow along. If not, let us know!