Go Back   Askaboutmoney.com > Don't Askaboutmoney > I.T.,Cameras and MP 3 players

Reply
 
Thread Tools
  #1  
Old 17-09-2010, 11:20 AM
Madilla Madilla is offline
Frequent Poster
 
Location: Monaghan
Posts: 301
Default Problem with date format on excel sheet

I have been sent a file with dates. Some of the dates are appearing in the incorrect format. One date shows 8/10/2010 when it should be 10/8/2010. When I look at format of cell it shows English UK.Other dates appearing correctly have just general format. The sender of the file(US subsidary) confirms it displays correctly on her side.Wondering if anyone can advise where the problem might arise from. Many Thanks
Reply With Quote
  #2  
Old 17-09-2010, 01:04 PM
alaskaonline alaskaonline is offline
Frequent Poster
 
Posts: 488
Default

does right click on the number and then "format cells" and then "date" not work for you? what excel version are you using? i have 2007 and 16 options.

you can also try the trick to copy/ paste it into a text pad and then back into a new excel sheet. worked for me before - why? - i honestly don't know.
Reply With Quote
  #3  
Old 17-09-2010, 05:24 PM
Madilla Madilla is offline
Frequent Poster
 
Location: Monaghan
Posts: 301
Default

I have no problem with working around this problem to fix on this file-changing format to US date sorts it. The difficulty is that this is date critical data and had the problem not been noticed could cause a lot of difficulties. I need to know why it is happening to ensure that in futre I can be confident in the integrity of my data.
Reply With Quote
  #4  
Old 17-09-2010, 08:37 PM
marksa marksa is offline
Frequent Poster
 
Location: Earth - Mostly,
Posts: 368
Default

maybe try changing the settings of your pc to US keyboard and regional settings in windows control panel before opening the file, and then see if it opens okay. slight pain I guess, but if it solves your problem it's probably worth it.
Reply With Quote
  #5  
Old 17-09-2010, 08:58 PM
ajapale ajapale is offline
 
Posts: 7,647
Default

are you sure that all your dates are in the Julian Date form? It is possible that some are simply in text format?
Reply With Quote
  #6  
Old 17-09-2010, 09:12 PM
allthedoyles allthedoyles is offline
Frequent Poster
 
Location: The South East
Posts: 1,018
Default

Quote:
Originally Posted by Madilla View Post
I have been sent a file with dates. Some of the dates are appearing in the incorrect format. One date shows 8/10/2010 when it should be 10/8/2010. When I look at format of cell it shows English UK.Other dates appearing correctly have just general format. The sender of the file(US subsidary) confirms it displays correctly on her side.Wondering if anyone can advise where the problem might arise from. Many Thanks
Hope you understand that 8/10/2010 is August 10th 2010 in the US and therefore if your excel file came from USA , the dates are correct.

Check that you are both using the same MS Office Excel , as MS 2003 is way different than MS 2007 .
Reply With Quote
  #7  
Old 17-09-2010, 10:04 PM
mathepac mathepac is offline
Frequent Poster
 
Location: In the sky with
Posts: 5,412
Default

Quote:
Originally Posted by Madilla View Post
... I need to know why it is happening to ensure that in futre I can be confident in the integrity of my data.
It seems strange that some cells with dates are OK and others are not. This would lead me to suspect that the originator does not know how to specify and maintain data formatting, validation and integrity rules on a column by column basis.

If the data-items in certain fields are critical, you may have no option but to design a template with the rules you wish to apply embedded and insist it gets filled out as you want, with supporting verification documentation if necessary.

As pointed out above, system-wide settings in the control panels may effect formatting in certain cells, I'd be surprised if the could override the application settings, but right now I have no way of checking this (no Windows machines in the house, TG). Again if necessary, insist that the spread sheet is completed and maintained on a system with the same region settings you have / need.
Reply With Quote
  #8  
Old 18-09-2010, 10:15 AM
Crugers Crugers is offline
Frequent Poster
 
Posts: 443
Default

Quote:
Originally Posted by Madilla View Post
I have been sent a file with dates...Wondering if anyone can advise where the problem might arise from.
I've found that this issue usually happens with data that is not created in Excel but exported/imported into Excel. In your case it might be that the data is imported into Excel in the States from a Txt file or CSV file. When it is done in the States, Excel gets it "right" - file is in mm/dd/yyyy format and is imported into Excel that expects and understands mm/dd/yyyy... That would explain why your colleague in the States says it is OK for her...

I export from Quickbooks to Excel and the 'date' column inevitably exports from Quickbooks as US date format (mm/dd/yyyy). When it arrives in Excel the dates for the first 12 days of any month display as dates and look as if they are dd/mm/yyy but Excel understands them as mm/dd/yyyy. (If you want to check what way Excel is understanding the date data you can use the "=CELL()" formula to test it i.e. to test date data in say cell A1 type =CELL("format",A1) in an empty cell. If the result is D4 Excel thinks it is one of the many Month/Day/Year formats, D1 means it thinks it is some type of Day/Month/Year format. There are other results but I don't think they are relevant here. Search Help for "=CELL()" to find listing or them...)

A quick check on the date data column, by making the 'date' column twice as wide, will show whether Excel recognises all the data as dates. Entries that are recognised as dates should right align and the rest, unrecognised 'dates', will left align in the column.

SUGGESTED SOLUTION:
When you open the file, first thing to do is select the date column only. Then use "Text to Column".

In Excel 2003:
Go to “Data”: “Text to Columns” and the “Convert Text to Columns Wizard” should start. Click “Next” and “Next” to get to Step 3 of 3 and in the "Column data format" (top right) select the radio button for “Date” and select “MDY” from the drop down and then click “Finish”. Now the dates should all be recognised as dates (and will right align in the column) but will display in Month/Day/Year format. You can then select them all and format them all into Day/Month/Year format.
In Excel 2007:
It is almost the same -
Go to “Data” tab: “Text to Columns” and the “Convert Text to Columns Wizard” should start. Click “Next” and “Next” to get to Step 3 of 3 and in the "Column data format" (top left) select the radio button for “Date” and select “MDY” from the drop down and then click “Finish”. Now the dates should all be recognised as dates (and will right align in the column) but will display in Month/Day/Year format. You can then select them all and format them all into Day/Month/Year format.
Phew...
It seems complicated and long winded but once you have done it a couple of times it is "simples" tch!
Hope this works for you...

Last edited by Crugers; 18-09-2010 at 10:24 AM. Reason: Clarity...
Reply With Quote
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +1. The time now is 04:46 AM.