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

 

[Phelony]

Nothing fancy, just an expression really in Access as below.

Expr5:Format([PostDate],"DD/MM/YYYY")

Everything record should be sequential when it comes into Excel with the only day gaps being weekend and bank holidays.

So I've got a summary page that lets me know of any records within 10 rows of each other that have a difference of more than 5 serial days to pick up on any that have slipped through the net.

EDIT - My problem is that the core data out of our system doesn't know what it is supposed to be, so if I get MM/DD/YYYY, the data doesn't know it's wrong and will just convert it straight into DD/MM/YYYY and apply the serial date accordingly no matter how it's formatted as DD/MM/YYYY. Which is why I keep track of the serial data.

 

[Norie]

Using Format could actually be the problem, or part of it

It returns a string/text and when that's imported to Excel some of
the 'dates' will be recognised as such and be converted to 'real' dates,
but the others will be kept as text.

Have you tried using DateValue instead of Format and if you do want the formatting, changing the Format property of the field in the query?

Perhaps worth a try.

 

[Phelony]

I've never experimented with datevalue() within Access to be honest, I'm more of an Excel person than Access so I've pretty much just build up a toolbox of knowledge as I've gone along.

But I'll investigate it and give it a test. This isn't a huge issue at my end, but when it is (as it often is in Q1 reporting where even the week numbers can get sucked in as months to some readers!) it's a pain to resolve.

Thanks for the suggestion. Your curiosity may have saved me a headache or two!

 

[Tracy4125]

I had the same problem of converting American dates to British dates, the formulae below works fine for me, hopefully it works for you too.

=IFERROR(DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2)), DATEVALUE(MONTH(A1)&"/"&DAY(A1)&"/"&YEAR(A1)))

 

[Drewgarry007]

I am trying to convert American dates to UK dates into the following format DD-MMM-YY

The thing is none of the above codes work for some reason. I am copying the raw data from a Notepad file into excell is that makes a difference ?

The dates I want to convert from American are in column F and want the results in column M

Also pls note the american dates that are copied into excell can be either MM/DD/YYYY or M/DD/YYYY

Can anyone pls help??

 

[GlennUK]

Do you want a formula to do this? You could just copy the dates into column M and do Data/Text to columns - Next, Next, set Column Data Format to be M/D/Y, Finish.

 

[Drewgarry007]

Hi there, I have tried this but it does not work. I think its due to the fact that I am coping the data from a Notepad and excell does not recognise the american dates as dates ??

Any other ideals ??

The formulas seem to work on some of the copied american dates but not all of them ?? To make things clearer, if I highlight column F then "Format cells"/"Number - General" some of the dates (but not all) get converted to a genaeral number ie 10/12/2011 = 40887

<TABLE style="WIDTH: 53pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=71 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id=td_post_279069 height=17 width=71>Fwd Date</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>40887</TD></TR>





<TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id=td_post_2676737 class=xl22 height=17 align=right x:num>40887</TD></TR>
<TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>40887</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17>10/13/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17>12/21/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17>12/21/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17>12/21/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17>12/21/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17>12/21/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>40857</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17>12/21/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17>12/21/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17>12/21/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17>12/21/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17>12/21/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17>12/21/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffcc99; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>40887</TD></TR></TBODY></TABLE>

The formulas work for the dates that stay as dates after the above test... however for the ones that change to eg 40887 the formulas do not work ??

 

[badass101]

Assuming A2 contains date formatted as 01/30/2012

=CONCATENATE(REPLACE(REPLACE(A2,1,3,""),4,7,""),REPLACE(A2,3,10,""),REPLACE(A2,1,5,""))

 

[Andrew74]

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

· US format puts date values in the following order : month, day and year.
· When in UK excel, if the ‘day’ value is less than 12, excel automatically formats the cell as a number. If this is the case, then you can use the formula =DATE(YEAR(cell ref),DAY(cell ref),MONTH(cell ref)) which just swaps around the day and the month.
· If the ‘day’ is greater than 12, UK excel gets confused and automatically formats the cell as text. If this is the case, then the following formula should be used = DATE(RIGHT(cell ref,4),LEFT(cell ref,2),MID(cell ref,4,2)). This formula also re-orders the date, but treats it as text.
· To work out which formula to use you need an “IF” formula to work out if you are dealing with a text format or a number format, and then apply the appropriate formula from the points above =IF(ISTEXT(cell ref), DATE(RIGHT(cell ref,4),LEFT(cell ref,2),MID(cell ref,4,2)), DATE(YEAR(cell ref),DAY(cell ref),MONTH(cell ref)))

I guess this should work the other way around too , if converting from UK to US format.

 

[niteslave]

Assuming A2 contains date formatted as 01/30/2012

=CONCATENATE(REPLACE(REPLACE(A2,1,3,""),4,7,""),REPLACE(A2,3,10,""),REPLACE(A2,1,5,""))

So this formula works for me however my date also has time so it is MM/DD/YYYY HR:MM

Obviously the time is correct as it is. As a work around I delimit the time, convert the date and then put it all together however there must be an easier way?