Mixed US and UK date formats in Excel Spreadsheet

[Buntrock]

PLEASE HELP


When exporting a spreadsheet out of SAP i am getting a mixture of date formats appearing in a column between US and UK so when i want to filter by that column and have the dates running oldest to newest, it will only pick up the UK dates. I have try to change the format of the US dates but can not seem to alter/edit them at all other than physically going and changing the date on all of them one by one.

Can anyone shed any light on this please?

PS its Excel 2007

[kbkumar]

Hi - Welcome to the forum.

Even if the dates are in different format(s), when you sort them they should be fine. Can you upload a sample workbook?

Thanks

[Buntrock]

When you sort them, it will only pic up the UK dates.

how do i upload?

[kbkumar]

To upload a workbook, click on "Go Advanced" button below and click on the attachment link that you would see....

[kbkumar]

Detailed steps in case it helps:

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Buntrock]

Hope this works

[Buntrock]

Think i might have stumped you all!

[kbkumar]

The problem I see is with the cells L208:L442 which are in US format. Copy these cells into a different sheet and apply Delimiters (Text to columns). To use Delimiters follow these steps:

1. Select your dates
2. Press Alt + D + E
3. Select Delimited, press next
4. Select Other and type "/" in the box next to Other
5. Finish

This will have all the 3 components of the date broken down into 3 columns. In the 4th column, use the below formula and copy down:

Please Login or Register  to view this content.

P.S: In this case the data is in row 207

Now this is in formattable position and can combine with your dates in the master file and the dataset would be in sortable manner.

Hope this helps.

[Buntrock]

I can only type one character in the box next to the word "other"

[JosephP]

are the uk dates actually correct as they are or have the day and month been transposed from what they ought to be? if they have been transposed then select column N data, choose data-text to columns, delimited, leave all options unchecked in the next screen, then on the last screen choose Data and MDY and press Finish. repeat for the data in column O

[Buntrock]

On a UK date format the it reads day/month/year but on the US version as you say it reads month/day/year...(transposed). This proves an issue when trying to filter the sheet to have the column running in date order as the filter will not recognise the US format

[Buntrock]

So yes the dates are correct......but a UK date will appear as 31/01/2012 for the 31st Jan 2012 but in US format it reads 01/31/2012

[JosephP]

are you sure the dates that are currently dates are actually correct? it would be very strange for excel to interpret some dates correctly as dd/mm/yyyy format and not others. it's far more likely that a date that was originally 12/01/2012 (i.e. 1 Dec 2012 in mm/dd/yyyy format) was interpreted as 12 Jan 2012 by a uk system but 12/31/2012 was left as text. what does the row 2 data look like in the SAP system?

[kbkumar]

Thats correct and you are required to type /

I can only type one character in the box next to the word "other"

[Buntrock]

Joseph.............yes, 100% correct because on row 208 column L it reads 01/14/2012 which should be the 14th Jan 2012 and on SAP it reads 14/01/2012

[JosephP]

I'm afraid you are missing my point-ignore the dates that are still in us format.

on row 2 of your sample workbook the dates are 12 Jan 2012 to 12 Feb 2012-is that what they are in SAP or are they 1 Dec to 2 Dec 2012?