Dates wrong format

[geotzi]

Hi,

I have an excel sheet with data that I extract from an online source. I have assigned a VBA code to this sheet in order to write some of these data in a new sheet. Some data are dates which i am taking in a wrong format. For example, I am taking dates in United Kingdom format (dd/mm/yyyy) and some other are shown as number. When I change the format of these numbers to dates I take the United States format (mm/dd/yyyy). I would like to have all the dates in (dd/mm/yyyy) United kingdom format.

Could you please help me with that?
Do you know if this an error that VBA code creates?
Is there any code that i can run to solve this error?

Thank you in advance.

Georgia.

 

[vrunda]

I think you have to change the date format of your PC from control panel to (dd/mm/yyyy) United kingdom format. Then when you change the format of numbers you would get correct format as above. Hope it helps.

 

[geotzi]

Hi, thanks for your reply. I have checked my PC and it has the right format (dd/mm/yyyy).

Try to explain my issue better:
I have a sheet1 which I extract dates from an online source. I changed all the dates to real Date in the sheet1. I have assigned the VBA code to sheet1 which write some of these dates in sheet2. I have also changed the format of column2 (data with dates) to real Date in sheet2 but i am taking either the dd/mm/yyyy or mm/dd/yyyy format in the column2. The most strange thing is that the sheet1 has all the dates with format dd/mm/yyyy.

I would like the same format for each date (dd/mm/yyyy) in sheet2/ column2.
Do you know if this is a problem that VBA code creates? If there is any solution?
thanks

 

[NARAYANK991]

Hi Georgia ,

I am somewhat confused ; is the data transfer to Excel being done by you manually , or by VBA code ?

If it is by VBA code , can you post the code here ?

If the dates are all in the same format on the webpage , why should some of them get transferred as numbers , and others as dates ?

What does this mean :

Some data are dates which i am taking in a wrong format.

Narayan

 

[geotzi]

No it is not manually. I have connected the Salesforce.com (online source) with my excel. This is a code that someone created to connect excel with Salesforce.

The sheet1 has the extract data. I have then assigned the following VBA code to sheet1. The code writes the close date and last modified date in the OpportunityTest sheet. When I run the code the dates are shown either with dd/mm/yyyy or with mm/dd/yyyy format in the opportunitytest sheet.
So i would like to have the dates with the same format dd/mm/yyyy. I am not sure why i am taking this error format although in the sheet1 all the dates have the same format dd/mm/yyyy.
thanks

*sorry for the complicated code I am new to VBA

Sub RefreshtableA()
    Dim id_op As String
    Dim i As Integer, j As Integer, Opportunitiestotal As Integer, counter As Integer

'  This cell refers to the total OpportunityTest every week
     Opportunitiestotal = Worksheets("Source").Cells(2, 5).Value
    For i = 2 To Opportunitiestotal
        id_op = Worksheets("OpportunityTest").Cells(i, 2).Value
        n = Worksheets("Sheet1").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
        For j = 2 To n
            If id_op = Worksheets("Sheet1").Cells(j, 2).Value Then
               Closedate = Worksheets("Sheet1").Cells(j, 7).Value
               LastModified = Worksheets("Sheet1").Cells(j, 11).Value

               Worksheets("OpportunityTest").Cells(i, 8).Value = Closedate
               Worksheets("OpportunityTest").Cells(i, 11).Value = LastModified
            End If
        Next j
    Next i
End Sub

Georgia.

 

[NARAYANK991]

Hi Georgia ,

Can you check which of the dates are in the wrong format ?

Can you verify whether the dates in the Sheet1 tab are all dates ? To check this , select the entire data range , and format it as General or Number , and confirm that all of them are getting converted to numbers ; for example today's date ( January 21 , 2015 ) , is really the number 42025.

Narayan

 

[geotzi]

Hi, when i change the data range (dates) in the sheet1 to general i am still taking dates with dd/mm/yyyy format.
When i change the data range to numbers i am again taking dates with dd/mm/yyyy format.

When i run the code i am taking either dd/mm/yyyy or mm/dd/yyyy in the Opportunitytest sheet. The only solution is to find the mm/dd/yyyy dates and change the format to Unites states and the date is shown as the Uk format dd/mm/yyyy. I am not sure how secure is this option.

Let me know if you suggest any other option and thanks f yr help.

 

[NARAYANK991]

Hi Georgia ,

When you change the format to General or Number , if the cell contents remain in date format , then it means that they are strings to start with.

What you can do is before running your macro , do a Text to Columns ; do not make any selection in the first 2 dialogs , but just click the Next button. Then select Date and the proper date format , which in your case would be DMY. Click on Finish. All of the text strings should be converted to numeric date values.

If required , make a copy of the text data to a separate column so that you can verify that the conversion has gone through correctly.

When you are sure , then run your macro.

Narayan

 

[geotzi]

Thanks a lot!!! It worked!!!!)))))