Solved

Date formatting from US to UK when cell is being read numerically instead of date format ?

Posted on 2016-08-09
8
49 Views
Last Modified: 2016-08-10
HI Guys

Hope you can help

Im having trouble converting a column with US dates as the section where it converts to UK dates still reads the data in numeric form (ie. instead of 08/01/2016 it still thinks the cell has 42325 (or whatever the conversion may be) as the result)

I have attached the sheet in question

Basically, the data gets pasted in and Column M contains the US dates
Column AT pulls the in the date whilst Column AU switched the date around to UK and then formats it in AV in order to split it into the yellow section for financial purposes.
However, when I paste the data in, and column AT populates the dates, column AU still believes the date in AT to be in numeric form and no matter how much I try to pre-format the cells, I cannot get this to change correctly.

Do you guys have any ideas as how to overcome this or is there a simpler method of converting the US date to UK format?

J
EE_Example.xlsm
0
Comment
Question by:Jase Alexander
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 95

Expert Comment

by:John Hurst
ID: 41749031
Dates as a number within the sheet are independent of format.

Set the format of the target column as UK format and when you paste data into it, use Paste Special, Values and they should then get the new format
0
 
LVL 23

Expert Comment

by:yo_bee
ID: 41749037
Right click on the column and select format.
Under Local change it to  English(United Kingdom)

img1
0
 
LVL 95

Expert Comment

by:John Hurst
ID: 41749045
If you paste US date into UK date, US date will be pasted in. You need to paste. special, values to keep the target formatting
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 41749048
If the suggestion from John Hurst is not possible, you can use this formula to transform the dates.

=IF(ISNUMBER(M2),DATE(YEAR(M2),DAY(M2),MONTH(M2)),DATE(RIGHT(M2,4),LEFT(M2,2),MID(M2,4,2)))

Open in new window

0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41749068
What I am missing here?

In Col. AT, you have formula =IF(M2="","",M2) to fetch the dates from col. M which are in US date format. Right?
Then why not custom format the col. AT itself with "dd/mm/yyyy" to show the dates in UK format?
And then in AW2 you can use the formula =TEXT(AT2,"mmm-yy") to show the date in AT2 as Mon-Year or whatever format you like.

Point to remember is by changing the date format doesn't change the underlying date in anyway, the date remains the same. Also excel treats dates as numbers, which you can see if you format a date cell with General format.
0
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 41749188
When dates as text from another system are pasted, Excel tries to help, and converts what looks as a date to a date, so when US dates has month <= 12, it is converted to UK dates, but when month > 12, it is pasted a text.
08/12/2016 will convert to 08-12-2016, but should be 12-08-2016.
08/13/2016 will be the text "08/13/2016" but should be 13-08-2016.
The formula above checks if it is a date (numeric), and switch day and month, and if it a text, extracts the year, month and day to make a real date.
0
 

Author Closing Comment

by:Jase Alexander
ID: 41750029
This worked perfect

Thank you so much (and everyone) for your help

Much appreciated

J
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41750034
If it is text values being pasted in, you can then use the Text to Columns wizard to convert to numbers.

In step 3 of the wizard, in the left hand side choose Date and then choose the dropdown for MDY which tells the conversion that the source is in US format. This will then ensure that all dates are inserted correctly.

The conversion to UK format can then be a simple =IF(M2="","",M2) as mentioned by neeraj above and then format this column as UK format.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question