Convert American dates to British dates

[Cara]

Is it possible to use a formula or cell formatting in Excel to convert a column of American dates mm/dd/yyyy to British formats dd/mm/yyyyy.
I have tried the conventional custom formatting but it doesn't modify the existing dates
Thanks

 

[Dabby]

To convert American date to English format
=DAY(A1)&"/"&MONTH(A1)&"/"&YEAR(A1) where date is shown in cell A1

to avoid any doubt go to Format/Cells/Date and select 7-Mar-2001 format so month is shown
or get date value by typing = in a spare spreadsheet cell and click on the cell containing the date

4-May-18 is 43224 5-Apr-2018 is 43195 both samples where 4/5 or 5/4 can cause confusion!

 

[MARK858]

To convert American date to English format
=DAY(A1)&"/"&MONTH(A1)&"/"&YEAR(A1) where date is shown in cell A1

That only works if you are on a computer where the Windows regional settings are US i.e. mm/dd/yyyy.

If the Windows regional settings are UK/English i.e. dd/mm/yyyy, then with the US date anything above the 12th day will be text and not a real date and anything up to the 12th day will give an incorrect day and month unless the day and month are the same number.

Excel Workbook
	A	B	C
1	10/20/2018	#VALUE!	20/10/2018
2	09/05/2018	9/5/2018	05/09/2018
General Data

Safe way is to have the cells formatted as a number when the data is sent then convert it to a date once received otherwise you end up using a formula like the one below (same formula as C1 in the table above).

=IFERROR(IF(ISNUMBER(A1),VALUE(TEXT(A1,"mm/dd/yyyy")),DATE(RIGHT(A1,4),LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,2))),DATE(RIGHT(A1,4),LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,1)))

The other safe way is to use the international date format yyyy-mm-dd.

 

[Dabby]

"That only works if you are on a computer where the Windows regional settings are US i.e. mm/dd/yyyy."

Thanks Mark, Problem arises when swapping OS from Win 7 to Win 10 then I lose English settings to Win 10 US settings. That messes up my spreadsheet dates until I contrive an Excel formula to revert 'em back to English dates. Don't wish to use the International date format as that is foreign to me.
Maybe I need to delve into Win 10 settings to tell it to favour English? Not sure where I do that.
​

 

[MARK858]

You just need to change your Windows 10 settings, nothing to do with Excel. I will post a link on how to do this when I get in.

 

[Dabby]

This Win 10 date problem has persisted since Win 10 was first installed 2 years ago and is still a problem on latest Win 10 64bit ver 17134rs4 with latest spring update! Settings show short and long dates as English UK format but spreadsheet dates show as USA format. Having got used to this I simply use excel formulas that reverse the awkward mm/dd positions to dd/mm. Have downloaded 64bit English UK Language pack this afternoon
lp_e23027acbe75972822e5564bfc32838515c41583.cab
but run/lpksetup warning says "The language pack cannot be installed on this computer"

 

[MARK858]

Have you checked that your default Excel date isn't set up as US format?

 

[Dabby]

Well done Mark! Yes I'm using Kingsoft free spreadsheet and US dates are the default.
Here is what Kingsoft say:
"In Kingsoft Spreadsheets Free 2012, the default date format is mm/dd/yyyy, it also offers function to change the date into dd/mm/yyyy."
Unfortunately the function to change date format to UK will change date appearance above to dd/mm/yyy but the underlying date value remains! So >
correct date value 43224 for UK 4-May-2018 will appear as underlying value 43195 which is wrong date of 5-Apr-2018.
There is no way to correct this in Kingsoft 2012 apart from building a formula to swap the day and month to conform to UK date format. ​

 

[Dabby]

Here is typical item on bank statement

in cell C1​

04/05/18 HARRODS LONDON PURCHASE £6.76
and here are formulae to read the date as UK format
=VALUE(MID(TRIM(C1),4,2)&"/"&LEFT(TRIM(C1),2)&"/"&MID(TRIM(C1),7,2))
=VALUE(MID(C1,FIND("/",C1)+1,2)&"/"&LEFT(C1,FIND("/",C1)-1)&"/"&MID(TRIM(C1),FIND("/",TRIM(C1),6)+1,2)) note final formula digit 2 for yy will be 4 if yyyy.

 

[SpillerBD]

No. Dates are serial values and it is only formatting.
If formatting doesn't change the appearance of what is in the cell, it has been read as text because Excel could not recognize the value as a valid date for the Region settings.
Take the following text from a CSV file, "04/01/2014"
When opened direct in Excel on a US region, that will be April 1, 2014. In GB, 4th of January, 2014.
So what was the date format in the CSV? US or GB?
That is why the Get External Data\From Text SHOULD be used because it gives the user the ability to define a field as a DATE field and define the structure of the date as DMY or MDY (or other combinations) to make sure the import provides the correct value.

Excel's assumptions can easily be wrong, so don't let it unless verified.

 

[MARK858]

Dabby, just to re-enforce what SpillerBD has stated with your example of 04/05/18.
First of all on a UK set up machine they would normally show as a valid UK date not text and so you would get a #VALUE error with the formula you posted.

Excel Workbook
	C	D
1	04/05/2018	#VALUE !
2		#VALUE !
Sheet3

If you put a ' in front of it to turn it to text then you get 05 April 2020 not 05 April 2018 (you have a 2 when it should be a 4).
The below date does have a ' at the front although Excel Jeanie doesn't show it as it only shows what is visible.

Excel Workbook
	C	D
1	04/05/2018	05 April 2020
2		05 April 2020
Sheet3

Btw, I did post a formula to convert US to UK for those that are text or appear as real (incorrect) dates in post number 32.