Imported data into excel unable to format

September 15, 2011 at 02:32:00
Specs: Macintosh
I have imported a lot of information from our database into excel but the dates are not recognised as dates which means I can't format them.

They currently come up as 2011-02-12, left aligned and non-format-able (?!)

I have nearly 2000 entries and really don't want to enter them all again manually to have the flexibility. I am adding to this spreadsheet all the time and need the data sort to recognise all the date entries in the same way.

Any help getting this data sorted in a quick way would be massively appreciated - if it is not possible to do it quickly would someone let me know that too?

Thanks


See More: Imported data into excel unable to format

Report •

#1
September 15, 2011 at 06:00:33
Dates imported into Excel is one of the most common issues discussed in this forum.

The problem is that the dates end up as text strings and Excel refuses to let go of that format.

What often works is to use the DATEVALUE function to force Excel to create a date serial number which can then be formatted as a date.

Dates and Times are store as "serial numbers" within Excel with the integer portion being the date and the decimal portion being the time.

1/1/1900 is Day 1, 9/15/2011 is Day 40801 (40,801 days since the beginning of Excel time)

The DATEVALUE function should return a 5 digit number which can then be formatted as a date. However, if there are spaces or hidden characters than came along when you did the import, you might have to use the TRIM or CLEAN functions to remove the extra characters so that DATEVALUE can work with the text string.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 15, 2011 at 08:01:19
Hi DerbyDad03

Thanks for the response - is there a way to use the DATEVALUE function along a whole column that has different values in each cell. It is working brilliantly (thank you) if I go cell by cell but not sure how to do it for the whole thing.

Any help greatly appreciated.


Report •

#3
September 15, 2011 at 10:23:41
There are a couple of ways to auto-fill your formula...

You can just drag it down the column.

e.g. Put =DATEVALUE(A1) in B1

Hover your mouse over the little black box in the lower right corner of B1.

When it turns in a +, click and drag down as far as you need to.

It will increment the Row number in each cell.

Another option is to double-left-click the plus sign and Excel will auto-fill the formula down along side your data to the bottom of the list. The one limitation is that it will stop if it encounters a blank cell in the adjacent column.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
September 16, 2011 at 09:43:02
If you're still around, I'd like you to try something else.

Select all of the "dates" that you can't format and do this:

Data...Text-To-Columns...Finish

Let me know if that converts them to real dates.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
September 18, 2011 at 13:30:25
Thanks so much for all the help - all the dates are now converted and I can format them to my hearts content!

Brilliant and reliable help. Thanks again.

Jo


Report •

#6
September 18, 2011 at 13:31:55
oh, sorry - i had already tried the text to columns without any luck.... the formula worked though.

thank you!


Report •

#7
September 18, 2011 at 19:38:45
I'm glad I could help.

Any chance you could tell me what kind of database you imported the dates from and how you did the import?

The reason I ask is that some dates work fine when imported/copied into Excel, others get fixed with Text-to-Columns, others need DATEVALUE, etc.

I've never been able to figure out why dates act so differently, so I'm going to start asking where the dates came from to see if I can figure something out.

Thanks.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question