Format Cells Date (or any change) not working on imported data

Hello,

I've just spent ages researching this and not come up with what I need
to be able to do.

I have a worksheet for some simple data that has been imported, a
date, text and number column (as they display graphically to the end
user). All are a "general" format when using Format > Cells.

The issue I have is that the date information is in an American date
format and I would like to change them into a UK date format. Format >
Cells and selecting any option (including custom and special) makes no
changes to the imported data.

I have seen the work arounds whereby you split out the individual day,
month and year into another cell. However this does not solve the over
riding issue that I cannot make any format change to these cells.

I've also seen the Data > Text to Columns and played with this to no
avail.

Why can't I change the cell formats on my imported data? How can I fix
this?

Many thanks for your help,
Jo
0
2/8/2008 12:07:01 PM
excel 39879 articles. 2 followers. Follow

3 Replies
474 Views

Similar Articles

[PageSpeed] 10

Hi Jo,

Formatting doesn't help because the cell is text, not a number.
If the data is imported via a file, rename that file to a .txt file; this will give you a menu to interpret the data (like as a 
date) when opening.
You can also use the Data>Text to columns command and make sure it is interpreted as a date

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel


<midnightjo@gmail.com> wrote in message news:28e9e2d5-092a-4ff5-a9c2-dabdb5f3c33e@h11g2000prf.googlegroups.com...
| Hello,
|
| I've just spent ages researching this and not come up with what I need
| to be able to do.
|
| I have a worksheet for some simple data that has been imported, a
| date, text and number column (as they display graphically to the end
| user). All are a "general" format when using Format > Cells.
|
| The issue I have is that the date information is in an American date
| format and I would like to change them into a UK date format. Format >
| Cells and selecting any option (including custom and special) makes no
| changes to the imported data.
|
| I have seen the work arounds whereby you split out the individual day,
| month and year into another cell. However this does not solve the over
| riding issue that I cannot make any format change to these cells.
|
| I've also seen the Data > Text to Columns and played with this to no
| avail.
|
| Why can't I change the cell formats on my imported data? How can I fix
| this?
|
| Many thanks for your help,
| Jo 


0
nicolaus (2022)
2/8/2008 12:21:01 PM
Hi

Just spent a while trying to figure that out myself...
there's bound to be more elegant solutions but I gave up looking
after a couple of hours. This is the formulae I eventually used.

Assuming the American date is in Cell A1, copy and paste this in A2
Then reformat A2 with the UK date. ...worked for me

=DATE((RIGHT(TEXT(A1,"m/d/yyy"),4)),(MID(TEXT(A1,"m/d/yyy"),3,1)),(LEFT(TEXT(A1,"m/d/yyy"),1)))


best of luck


<midnightjo@gmail.com> wrote in message 
news:28e9e2d5-092a-4ff5-a9c2-dabdb5f3c33e@h11g2000prf.googlegroups.com...
> Hello,
>
> I've just spent ages researching this and not come up with what I need
> to be able to do.
>
> I have a worksheet for some simple data that has been imported, a
> date, text and number column (as they display graphically to the end
> user). All are a "general" format when using Format > Cells.
>
> The issue I have is that the date information is in an American date
> format and I would like to change them into a UK date format. Format >
> Cells and selecting any option (including custom and special) makes no
> changes to the imported data.
>
> I have seen the work arounds whereby you split out the individual day,
> month and year into another cell. However this does not solve the over
> riding issue that I cannot make any format change to these cells.
>
> I've also seen the Data > Text to Columns and played with this to no
> avail.
>
> Why can't I change the cell formats on my imported data? How can I fix
> this?
>
> Many thanks for your help,
> Jo 

0
zerosky (3)
2/8/2008 2:48:39 PM
Aha! That's genius (and made me look rather intelligent at work, so
always useful!). I thought I had tried some permutation of that, but
obviously not.

Thanks very much,
Jo

On Feb 8, 12:21=A0pm, "Niek Otten" <nicol...@xs4all.nl> wrote:
> Hi Jo,
>
> Formatting doesn't help because the cell is text, not a number.
> If the data is imported via a file, rename that file to a .txt file; this =
will give you a menu to interpret the data (like as a
> date) when opening.
> You can also use the Data>Text to columns command and make sure it is inte=
rpreted as a date
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> <midnigh...@gmail.com> wrote in messagenews:28e9e2d5-092a-4ff5-a9c2-dabdb5=
f3c33e@h11g2000prf.googlegroups.com...
>
> | Hello,
> |
> | I've just spent ages researching this and not come up with what I need
> | to be able to do.
> |
> | I have a worksheet for some simple data that has been imported, a
> | date, text and number column (as they display graphically to the end
> | user). All are a "general" format when using Format > Cells.
> |
> | The issue I have is that the date information is in an American date
> | format and I would like to change them into a UK date format. Format >
> | Cells and selecting any option (including custom and special) makes no
> | changes to the imported data.
> |
> | I have seen the work arounds whereby you split out the individual day,
> | month and year into another cell. However this does not solve the over
> | riding issue that I cannot make any format change to these cells.
> |
> | I've also seen the Data > Text to Columns and played with this to no
> | avail.
> |
> | Why can't I change the cell formats on my imported data? How can I fix
> | this?
> |
> | Many thanks for your help,
> | Jo

0
2/12/2008 9:49:35 AM
Reply:

Similar Artilces:

Conditional formatting macro
Help. I would like to know if it is possible to have a macro that changes the colour of a cell dependant on the number or text within. eg If the following cells were populated as follows a1=1 Make the cell Green =2 Make the cell Red =3 Make the cell Blue =4 Make the cell Orange and so on till 7 Is this at all possible ? I know that i can colour cells 3 times with simple conditional formatting, but would like to run upto seven different colours. any help would be appreciated. Steve '----------------------------------------------------------------- Private Sub Worksh...

How do you change the background color of a picture?
I have a image that I have copy and pasted, however I want to fill behind the image. I know how to fill and all that, my problem is that it recognizes the entire image as a picture, I was wondering if there is a way to change the background while the image lays on top? sureisdifferent wrote: > I have a image that I have copy and pasted, however I want to fill > behind the image. I know how to fill and all that, my problem is that > it recognizes the entire image as a picture, I was wondering if there > is a way to change the background while the image lays on top? =============...

visible cell only
I'd like to use the PERCENTILE function in a list that has been autofiltered and get the results based only on the visible cells. I've used SUBTOTAL in order to get count, average, min and max. But I need to get the .25 and .75 percentile figures for the filtered data (visible cells only). I've scoured these forums. I've scoured the web. I've found some vba code that was supposed to select only visible cells but it doesn't work for me. I posted last week in the programming section of these forums (and again this morning) but got no reply. I figure...

Using part of a cell in a chart title
I have a chart which should get a title. However, this should be partly be used from a cell e.g. "counted with 5%" 5% should be taken from the cell and used in the title. Is this possible? Hi, Yes it's possible but all of the chart title needs to be in the cell. So you may need to use a helper cell and concatenate text and value. http://www.andypope.info/tips/tip001.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Nicole" <Nicole@discussions.microsoft.com> wrote in message news:5CB7A971-AA7F-4C34-BB42-7DC283AA2958@micro...

Sort ascending, make changes, restore previous order
I've got an AutoFilter in a spreadsheet. I want to sort ascending, mak some changes to some cells, then restore the previous order. Can thi be done easily or will this require some programming?? Thanks in advane! Matt -- BVHi ----------------------------------------------------------------------- BVHis's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=859 View this thread: http://www.excelforum.com/showthread.php?threadid=47508 I'd use a helper column. Put =row() and drag down. Convert it to values (edit|copy, edit|paste special|values) Do all y...

Parsing data from one spreadsheet into another format
The data that we dump out of one machine comes in like below. %AT_1300 Bottoms|Conductivity| (Water Out) InputRange VDC1to5 %AT_1300 Bottoms|Conductivity| (Water Out) Custom_Range_Low 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) Custom_Range_Hi 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) MinScale 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) MaxScale 20.0 %AT_1300 Bottoms|Conductivity| (Water Out) EngUnits mhos %AT_1300 Bottoms|Conductivity| (Water Out) StepResponseTime 1.0 %AT_1300 Bottoms|Conductivity| (Water Out) DigFiltTimeCnst 0.016 And I need to convert this data to this f...

data input in text box
We have a form which the operator enters data in a text box. Currently we have a 'done' button on the form that the operator clicks to send the text box info to a vba program. How can we send the text box info to the vba program when the operator hits the enter key @ the end of the data entry for the text box? TIA -- _______________________________ In Christ's matchless name ted & colleen n6trf kc6rue Use the control's AfterUpdate event. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ted" <n6trf@arr...

Transferring over outlook data to new XP machine
How do I transfer over my old emails, address book to my new XP machine? I have looked over the internet and found nothing the tells me EXACTLY how to do this, any help would be greatly appreciated. senior_tech@yahoo.com If your using MS Outlook copy your .PST file across and import it into the new install. >If your using MS Outlook copy your .PST file across and import it into the new install. No, don't import it. Simply use "File">"Open" -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapids, MI 49512-1991 Brian.Tillman is the nam...

Auto-format in Microsoft Excel.
Each time i try to enter a number range, for example, 8- 10, in Excel, it constantly re-formats it to a date. If I change the formatting to "General" it turns it into a random number, usually 38209. I would like to turn off ALL auto-formatting, but that's probably asking too much. How do I disable this frustrating feature? thanks. Hi Paul When you enter "8-10" in a cell, that's not strictly a number but Excel thinks you mean a date so converts it as such. A date is a number and in your example the number 38209 represents August 10 2004 which is not a ra...

Outlook needs Outlook Express to work
I've installed my Outlook 2002 on my new Vista PC (I had Xp before). Now when I start Outlook it says "requires Outlook Express 4.01 or greater". I've been looking for Outlook Express to download and install but no joy. No CD came with my new Vista PC. Any ideas, short of buying a more modern Outlook? Vista doesn't actually support Outlook 2002 or older. Outlook Express is part of Internet Explorer but with the advent of Vista, it was switched to Windows Mail. There is no more Outlook Express. You could attempt however to create a profile using workgroup or c...

MS Money 95 data files
I hope that some one can answer this for me. I have used MS Money 95 for years, and it works just fine for me on Windows XP, however, I now have to reformat my hard drive, and have discovered that I can nolonger find my original install disk. Will the latest versions of Money still read the MS Money 95 data files. All that I have ever used the program for is to track my investments, and am unlikely to do any different in the future. Thanks Stan B In microsoft.public.money, Stan Banner wrote: >I hope that some one can answer this for me. >I have used MS Money 95 for years, and...

New Disk storage
Hi, I'm planning on adding a new SAN to our Exchange system (Active/Passive 2 node cluster) and this has been connected to the Exchange cluster nodes and is accessible as shared disk storage. But that isn't the question I've got, basically the mail stores are currently located on a Powervault (Drive S:) and I need to present a plan for moving this data (14 databases) onto the SAN storage, I can see two options Option1) Use the ESM to migrate databases to new drive letter 1. Use exmerge to export all email to PST files (and ensure backup) 2. use the ESM utility to change the...

move cell contents
Is there a way to move a cell contents to another cell with a formula. ex: if a5="Name" then move g5 to j5? Also, I am using =INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names from a list. I have the formula in different place pick random names from different list. This does work, but I have different list with some of the same names and with the random pick I do not want the same name to appear. -- Thanks for any and all help. Davidl Hi David a formula can only affect the cell it is in, it can't move or change another cell for this you need some code ...

How to keep format when importing Excel into SQL.
Can anybody help me please? When I import Excel file into SQL, a field that formated as 0000000000 (custom), loosing leading zeros. I tried to change data type in SQL after importing. No luck. I appretiate any help. See if using IMEX=1 helps: http://pratchev.blogspot.com/2007/10/importing-excel-data-into-sql-server.html -- Plamen Ratchev http://www.SQLStudio.com Plamen, I tried this, but got an error: "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered." Thank you very much for looking into this. "Plamen Ratchev" wr...

Change File Locations to Private Drive (not Folder)
Hi, I know that I can use "File Locations" in "Options" so that whenever I use the "Open..." command in Word, it will open to a specific folder. I'm wondering if there is a way that this can be applied not to a folder but to a specific drive on a network. Our company has a main public drive and has also assigned each of the Staff their own private drive. Is there a way to access the "main page" (for lack of a better term) of my private drive each time I use "Open..." in Word? Right now it goes to "My Computer" or ...

How to change font size on formula bar in Excell 2007
I don't find Tools>Options>General (as suggested in other post answers) in Excel 2007. On the Office button there is an Excel Options but it doesn't provide a method of changing font size on the office but. My font is so small I can barely see it. Office button>ExcelOptions>Popular tab>in the "When creating new workbooks" section, choose font and font size -- Kind regards, Niek Otten Microsoft MVP - Excel "jimwillie" <jimwillie@discussions.microsoft.com> wrote in message news:588AAC05-0F52-404E-AA01-128E70E02D0B@microso...

HELP! Need to export hourly sales data on POS (NOT RMS)
How can I export hourly sales data across a date range? For instance, I want to show hourly sales for the month of October so I can graph it and post it in our break room. If I can't export hourly data, can I export daily sales? The built-in reports don't address this data format. This is a multi-part message in MIME format. ------=_NextPart_000_008E_01C826DC.CBC512D0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit Mark, This should work for you. Keep in mind it takes up to 5-10 minutes to load...

How can I change 'Normal' Style for Word e-mails to 'Normal (Web)'?
Hi, I'm using Word as my e-mail editor in Outlook 2003 and want to change the default Style for e-mails from 'Normal' to 'Normal (Web)'. The problem is that new e-mails and replies in HTML format use the 'Normal' Microsoft Word 'Style', and this has no gap after paragraphs. The upshot of this is that when sending an e-mail, I have to press return twice to create a gap, but when the recipient views this, their software shows it as four gaps (the extra carriage return I typed + their correctly viewed HTML carriage return after each line). E.g. I type this: ...

Phone number normalizing and auto-formatting
I really appreciate the way that Outlook auto-normalizes phone numbers - for example, here in the US, I can enter 5555555555 and Outlook automagically formats that to (555) 555-5555. Why CRM 3.0 doesn't do this out of the box is strange but if anyone has ideas on how to implement, that'd be greatly appreciated. I've worked very hard to normalize all my data before import, but I know it's a matter of time before our users (ok, our sales people) destroy all that hard work by entering garbage into every phone field entry that the system cannot auto-normalize and error ch...

Importing leads and workflow
We are just implementing CRM and have a couple of lead lists that we want to import (about 5000 records). The sales force would like calls automatically scheduled when the list is imported and would only like 50 calls scheduled per week. I know that I can assign leads based on various criteria to the correct salesperson. However, is there any way that I can create the phone activities and only limit to 50 per week? Does workflow look at the salesperson work schedule at all or will each sales person have 1000 phone calls scheduled for 3 days from when I import? There isn't anything in ...

Grammar check not working
Hello, I am using Word 2007 and have a problem with grammar and punctuation errors. I deliberately put two spaces between words, do not put space after a comma, write long sentences and finish a sentence without a verb but the green underline never appears. The spell check is functioning properly, no problem with that. In Word Options > Proofing, "Mark grammar errors as you type" is selected. I changed that selection and tried again but it still did not work. I used different languages as default language but no change. I would be grateful if someone could come...

formula based on format
Is there a way to have a formula that is based on a cell colour or based on the way a cell is formated? I need it to count the number of occurrences that this happens. -- Thank you for your time. Windows NT Office 97 Hi you'll need VBA. See for an example: http://cpearson.com/excel/colors.htm "James Kendall" wrote: > Is there a way to have a formula that is based on a cell colour or based on > the way a cell is formated? I need it to count the number of occurrences > that this happens. > -- > Thank you for your time. > Windows NT > Office 97 This ...

Sorting Data #5
Is there formula or anyway to be able sort the below data into a format that I could create a pivot table on? I spend to many hours doing this every month. Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC Due Date: 1/16/1950 Total Payable: $100.00 Reference: Freight: Account #: 1234 Description: Name Reference 1 Amount: $100.00 Account #: 4321 Description: Name Reference 2 Amount: $100.00 Account #: 9876 Description: Name Reference 3 Amount: $100.00 Any help would be much appreciated!! You need to show a Before and After version. You still might not get any help, but your ...

what's the formula for adding symbols in cells?
I have a chart that has blank info in the legend. I want to add an * to indicate something, but just inserting a symbol doesn't work. Any ideas? Thanks. Debi - To add information to the legend, you need to add to a series name. Right click on the chart, select Source Data from the pop up menu, click on the series tab, select a series, and either type something in the name box, or click in it and select a cell with the mouse. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Debi wrote: > I have a chart tha...

HELP! remote data not accessible msg
Hello, I have a use who is currently using a Bloomberg DDE add- in. Whenever he attempts to activate the add-in to retreive remote data, the system hangs. If I go to task manager, I then see a message stating "Remote data not accessible. To access this data Excel needs to open another program.... I have searched the knowledge base and didn't find much help. Does anyone have any ideas? I am desperate!!!! We are currently using Excel 2003 in XP Professional. TIA, Ramissah ...