Re: DATES ARE SCRAMBLED!

[David Biddulph]

Before you do your import, set your Windows Regional Setiings to a format
appropriate to your data.
Alternatively, import as text, and then use Data/ Text to Columns and set
the date format appropriate to your input data at the final stage of the
text import wizard.
--
David Biddulph

"FARAZ QURESHI" <FARAZQ...@discussions.microsoft.com> wrote in message
news:4AEA07E6-D6D9-4382...@microsoft.com...
> Upon exporting a data from another and importing therefrom from in excel I
> have come across the following problem:
>
> The date format in the source data was m/d/yyyy, whereas I have the data
> format in destination file to be formatted as d/m/yyyy.
>
> Source data like 2/13/2000 has been converted to string as 2/13/2000 as
> there is no 13th month, but 11/2/2000 has been converted to 11/2/2000
> (correct date format but INCORRECT VALUE of 11-Feb-2000, actual one being
> 2-Nov-2000).
>
> How to get the correct dates in format of d/m/yyyy.
>
> Thanx in advance!
>
> FARAZ

[macropod]

Hi Faraz,

The following macro converts date strings and values in the selected range between mm/dd/yyyy and dd/mm/yyyy. The output is in the
form of date values that you can format as dates via Format|Cells|Number|Date.

Sub ConvertDateFormat()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
If Selection.Cells.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection
End If
With Application
On Error Resume Next ' In case there are no xlConstants or convertible dates in DtRange
For Each oCell In DtRange.SpecialCells(xlConstants)
oTxt = "'" & oCell.Text
oCell.Value = Mid(oTxt, 2, .WorksheetFunction.Find("/", oTxt) - 2) & "/" & Mid(oTxt, _
.WorksheetFunction.Find("/", oTxt) + 1, .WorksheetFunction.Find("/", _
.WorksheetFunction.Replace(oTxt, .WorksheetFunction.Find("/", oTxt), 1, " ")) - _
.WorksheetFunction.Find("/", oTxt) - 1) & "/" & Right(oTxt, Len(oTxt) - .WorksheetFunction.Find("/", _
.WorksheetFunction.Replace(oTxt, .WorksheetFunction.Find("/", oTxt), 1, " ")))
Next oCell
End With
End Sub

Note that no conversion is performed if the date is embedded in a formula or the output would be an invalid date.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

[FARAZ QURESHI]

Cant there be a simple formula using functions such as FIND, FORMAT, LEFT,
RIGHT?

[Pete_UK]

You could use a formula like this:

=DATE(YEAR(A1),DAY(A1),MONTH(A1))

to change a date like 2/11/2008 in A1 into 11/02/2008. Is this what
you want?

Pete

On Feb 12, 9:26 am, FARAZ QURESHI

> > "FARAZ QURESHI" <FARAZQURE...@discussions.microsoft.com> wrote in messagenews:4AEA07E6-D6D9-4382...@microsoft.com...

> > > Upon exporting a data from another and importing therefrom from in excel I
> > > have come across the following problem:
>
> > > The date format in the source data was m/d/yyyy, whereas I have the data
> > > format in destination file to be formatted as d/m/yyyy.
>
> > > Source data like 2/13/2000 has been converted to string as 2/13/2000 as
> > > there is no 13th month, but 11/2/2000 has been converted to 11/2/2000
> > > (correct date format but INCORRECT VALUE of 11-Feb-2000, actual one being
> > > 2-Nov-2000).
>
> > > How to get the correct dates in format of d/m/yyyy.
>
> > > Thanx in advance!
>

> > > FARAZ- Hide quoted text -
>
> - Show quoted text -

[FARAZ QURESHI]

Thanx Pete,

Just came up with a solution of my own as:

=TEXT(IF(ISNUMBER(D9),MONTH(D9)&"/"&DAY(D9)&"/"&YEAR(D9),MID(D9,FIND("/",D9,1)+1,LEN(D9)-FIND("/",D9,4)+1)&"/"&LEFT(D9,FIND("/",D9,1)-1)&"/"&RIGHT(D9,LEN(D9)-FIND("/",D9,4))),"DD/MM/YYYY")

YAHOO!!!

[Pete_UK]

Your formula will convert it into a text date format, but if you want
to do anything with the dates you will need them to be numbers - just
add zero at the end of the formula to do this (you may need to format
the cell as an appropriate date).

Pete

On Feb 12, 9:59 am, FARAZ QURESHI

<FARAZQURE...@discussions.microsoft.com> wrote:
> Thanx Pete,
>
> Just came up with a solution of my own as:
>

> =TEXT(IF(ISNUMBER(D9),MONTH(D9)&"/"&DAY(D9)&"/"&YEAR(D9),MID(D9,FIND("/",D9­,1)+1,LEN(D9)-FIND("/",D9,4)+1)&"/"&LEFT(D9,FIND("/",D9,1)-1)&"/"&RIGHT(D9,­LEN(D9)-FIND("/",D9,4))),"DD/MM/YYYY")

> > > - Show quoted text -- Hide quoted text -

[FARAZ QURESHI]

I am really very thankful Pete,

However, my formula is working fine.

[Pete_UK]

Okay, thanks for feeding back.

Pete

On Feb 12, 11:32 am, FARAZ QURESHI

<FARAZQURE...@discussions.microsoft.com> wrote:
> I am really very thankful Pete,
>
> However, my formula is working fine.
>
>
>
> "Pete_UK" wrote:
> > Your formula will convert it into a text date format, but if you want
> > to do anything with the dates you will need them to be numbers - just
> > add zero at the end of the formula to do this (you may need to format
> > the cell as an appropriate date).
>
> > Pete
>
> > On Feb 12, 9:59 am, FARAZ QURESHI
> > <FARAZQURE...@discussions.microsoft.com> wrote:
> > > Thanx Pete,
>
> > > Just came up with a solution of my own as:
>

> > > =TEXT(IF(ISNUMBER(D9),MONTH(D9)&"/"&DAY(D9)&"/"&YEAR(D9),MID(D9,FIND("/",D9­­,1)+1,LEN(D9)-FIND("/",D9,4)+1)&"/"&LEFT(D9,FIND("/",D9,1)-1)&"/"&RIGHT(D9­,­LEN(D9)-FIND("/",D9,4))),"DD/MM/YYYY")

[macropod]

Yes, you can use a formula, but that means having both sets of 'dates' in the workbook - and using a range to recalculate the dates.
The macro converts the dates in-situ without the need for that extra range. If you add it to your personal.xls workbook, the macro
will be available for any worksheet you might need it for, without having to add the macro to that worksheet's workbook.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"FARAZ QURESHI" <FARAZQ...@discussions.microsoft.com> wrote in message news:9E7862E6-4FF4-4893...@microsoft.com...