DATES ARE SCRAMBLED!

F

FARAZ QURESHI

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
 
D

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.
 
M

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
 
F

FARAZ QURESHI

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

macropod said:
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 said:
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
Click to expand...
Click to expand...
 
P

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
 
F

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!!!
 
P

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

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 said:
You could use a formula like this:
=DATE(YEAR(A1),DAY(A1),MONTH(A1))
Click to expand...

to change a date like 2/11/2008 in A1 into 11/02/2008. Is this what
you want?
Pete
Click to expand...
Cant there be a simple formula using functions such as FIND, FORMAT, LEFT,
RIGHT?
:
Hi Faraz,
The following macro converts date strings and values in the selectedrange 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]
-------------------------
Upon exporting a data from another and importing therefrom from inexcel 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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
P

Pete_UK

Okay, thanks for feeding back.

Pete

I am really very thankful Pete,

However, my formula is working fine.



Pete_UK said:
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
Click to expand...
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!!!
:
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
Cant there be a simple formula using functions such as FIND, FORMAT, LEFT,
RIGHT?
:
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]
-------------------------
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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
M

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

macropod said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

cannot change date format 4
Formatting Dates from Text to Numbers 2
Length of time from date/time custom format 2
Excel Help with dates 2
Format Changes When Using CopyFromRecordSet 0
Changing date to eliminate day in format mm/dd/yyyy 1
Transpose, but how? 0
Losing date format when saving .csv files 10

Top