Convert Date

[skc]

Hi,

I have a spreadsheet that is an export from Quickbooks -
an accounts package.

What has happened is that some dates are in Date format
(dd/mm/yyyy) but others are either in US format
(mm/dd/yyyy) or in Text.

How can I convert the whole column to UK date format
(dd/mm/yyyy).

Is there a function?

Thanks,

Skc

 

[Norman Harker]

Hi skc!

Caution!! Even though some of the dates are recognised and some are
not, the dates that are recognised are wrong! What happens is that
mm/dd/yyyy dates are "recognised" if the day is <=12 but rather than
this being regarded as the day number, it is treated as the month
number. Dates with day >12 are not recognised and are treated as text.

Probably the easiest solution is to change your Regional settings to
US settings *before* you import the data. After import of the data you
can change the settings back to UK.

Change settings using:

Close Excel
Start > Settings > Control Panel > Regional options
Change the country to (sic) English (United States)
Apply [IMPORTANT]

If your data source is no longer available, you can use the following:

=IF(ISTEXT(A2),DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)),DATE(YEAR(A2),
DAY(A2),MONTH(A2)))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[mangesh]

hi
use
=text(a1,"dd/mm/yyyy")

manges

 

[Dave Peterson]

One more option if you're importing from a .txt file.

Specify the correct date format for that field (mdy or dmy???).