DATES ARE SCRAMBLED!
 

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

DATES ARE SCRAMBLED!
 

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" wrote in message
...
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

DATES ARE SCRAMBLED!
 

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" wrote in message ...
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

DATES ARE SCRAMBLED!
 

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

"macropod" wrote:

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" wrote in message ...
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

DATES ARE SCRAMBLED!
 

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
wrote:
Cant there be a simple formula using functions such as FIND, FORMAT, LEFT,
RIGHT?



"macropod" wrote:
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" wrote in ...
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 -

DATES ARE SCRAMBLED!
 

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" wrote:

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
wrote:
Cant there be a simple formula using functions such as FIND, FORMAT, LEFT,
RIGHT?



"macropod" wrote:
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" wrote in ...
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 -

DATES ARE SCRAMBLED!
 

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
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")

YAHOO!!!



"Pete_UK" wrote:
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
wrote:
Cant there be a simple formula using functions such as FIND, FORMAT, LEFT,
RIGHT?

"macropod" wrote:
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" wrote in ...
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 -- Hide quoted text -

- Show quoted text -

DATES ARE SCRAMBLED!
 

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
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")

YAHOO!!!



"Pete_UK" wrote:
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
wrote:
Cant there be a simple formula using functions such as FIND, FORMAT, LEFT,
RIGHT?

"macropod" wrote:
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" wrote in ...
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 -- Hide quoted text -

- Show quoted text -

DATES ARE SCRAMBLED!
 

Okay, thanks for feeding back.

Pete

On Feb 12, 11:32*am, FARAZ QURESHI
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
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")

YAHOO!!!

"Pete_UK" wrote:
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
wrote:
Cant there be a simple formula using functions such as FIND, FORMAT, LEFT,
RIGHT?

"macropod" wrote:
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" wrote in ...
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 -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

DATES ARE SCRAMBLED!
 

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" wrote in message ...
Cant there be a simple formula using functions such as FIND, FORMAT, LEFT,
RIGHT?

"macropod" wrote:

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" wrote in message
...
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