Hi,

I have a system that exports all data into Excel in US date format (i.e.
mm/dd/yy), but as my customers are using UK formatting I need to convert
these dates to UK format (i.e. dd/mm/yy).

Can anyone help me with a macro or formula to assist me converting these?
There are SO many cells I need to convert doing it manually is not a feasible
option.

Thanks in advance,

IF the date is being stored in Excel as a date, then select the column
and format it as DD/MM/YYYY and job is done. IF the information is
being misinterpreted, then you would need to split out the day, year
and month parts of the date and turn them back into a date serial in
the correct order.

Hi Louise,

As a complement :
If Dat is your variable ...

Format(DateSerial(Year(Dat), Month(Dat), Day(Dat)), "Short Date")

HTH
Cheers
Carim

Aidan is obviously entirely correct, but I thought I'd add a few bullets to
help out a bit as well:

1) One pretty quick way of checking whether Excel has recognised that the
contents of a cell is in fact a date rather than a generic text string is to
look at how the cell contents have been justified: by default, Excel
left-justifies generic text, but normally right-justifies dates. However, if
the cell justification has been set manually, then this simple check will
obviously not tell you anything at all.

2) You can easily right-click on a cell to bring up the cell context menu,
and then select the Format Cells... menu item. In the Format Cells dialogue,
check the Category setting of the Number tab. If it says Date for the cells
you're checking then you're lucky, and if it says General you've got a bit of
work to do.

3) Of course, you can put together a VBA method that iterates over all cells
of all sheets, and if the data entered in a particular cell looks like an
American date ("mm/dd/yyyy") but due to some reason or another is not
properly recognised as a date, you could let the macro patch it up.

4) However, if the people editing this workbook have indeed put US formatted
dates into the worksheets, and if they were indeed using US settings, then
everything should be all right.

5) Ideally, you should NOT try to change the date formats of cells manually
-- let Excel handle it, and just make sure your users know that they are
running the system with US or UK settings. In theory, everything should work
transparently and no one should have to bother about changing date formats
explicitly. Check your "Regional and Language Options" of your Windows
system, select the Regional Settings tab, and make the necessary adjustments
(English US or UK). It Should Just Work (tm) :-)

Hope this helps,
/MP

" wrote:

IF the date is being stored in Excel as a date, then select the column
and format it as DD/MM/YYYY and job is done. IF the information is
being misinterpreted, then you would need to split out the day, year
and month parts of the date and turn them back into a date serial in
the correct order.

A good way of handling dates that may be in UK or US format is to make
the cells DD MMM YYYY format (or MMM DD YYYY as you see fit) which puts
the month name into alpha format. Another thought I should mention is
that I have many programs which extract dates from mainframe systems,
and I ALWAYS pull in the dateserial of the day, month and year part of
the date rather than relying on Excel to interpret the input. When
items are input manually, then have the cell pre-formatted as a date,
and also use data validation to ensure that they PUT dates - many users
think that 01.01.01 is OK as a date!

Make sure US date in column A is formatted as Text, then enter:
=DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,4,2))

HTH
--
AP

"Louise" a écrit dans le message de
...
Hi,

I have a system that exports all data into Excel in US date format (i.e.
mm/dd/yy), but as my customers are using UK formatting I need to convert
these dates to UK format (i.e. dd/mm/yy).

Can anyone help me with a macro or formula to assist me converting these?
There are SO many cells I need to convert doing it manually is not a
feasible
option.

Thanks in advance,

No need for the DateSerial function. Just use

Debug.Print Format(Now, "short date")


"Carim" wrote in message
oups.com...
Hi Louise,

As a complement :
If Dat is your variable ...

Format(DateSerial(Year(Dat), Month(Dat), Day(Dat)), "Short
Date")

HTH
Cheers
Carim

(a) how does this help with the question asked, as NOW gives todays
date
(b) in the original question, we may very well need date serial as this
stops Excel mis-interpreting the date - I'm in the UK and I can promise
you that from VBA Excel DELIGHTS in assuming I meant an American date
if it could possibly interpret it that way - DateSerial stops that from
happening!

(a) how does this help with the question asked, as NOW gives todays
date
(b) in the original question, we may very well need date serial as this
stops Excel mis-interpreting the date - I'm in the UK and I can promise
you that from VBA Excel DELIGHTS in assuming I meant an American date
if it could possibly interpret it that way - DateSerial stops that from
happening!