Need help for Change day to month and month to day

[ahaque]

Dear all
I need one help, I collect some data by CSV file which i save as xlsx, I found one big miss match on date field. Like 12the may 2011 but its showing 5th December 2011. please help anyone have any solution.
NOTE: I not advance user, basic user of excel.

BR_No Sec Ticket Arival_time WT Close_time
001 0 1 12/05/2011 13:15 1:30 12/05/2011 13:18
012 1 1 08/05/2011 10:00 10:15 08/05/2011 10:28

thanks in advance

-- removed inline image ---

 

[ahaque]

I downloaded the file. How do you know which dates are wrong?

M.

Thanks for your patients........
If you make Auto filter and check date fields.......
OR
in date fields if you make Custom Format dd-mmm-yyyy hh:mm

you will find some data available in June july August....Desember
[Note: this data i export on May, so data should be with in May month only]

Sorry to Disturb you all.

 

[Marcelo Branco]

So, are you saying that any date greater than 31/05/2011 is wrong?

If so try this (please, use a test-workbook)

Filter mannualy column F of Combine Sheet to show only dates greater than 31/05/2011 and try the macro below

Sub test()
    Dim lastRow As Long, rng As Range, aCell As Range
    
    With Sheets("Combine Sheet")
        lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
        Set rng = .Range("F1:F" & lastRow).Offset(1, 0).SpecialCells(xlCellTypeVisible)
        For Each aCell In rng
            aCell.Value = Format(aCell.Value, "dd/mm/yyyy hh:mm")
        Next aCell
    End With
End Sub

HTH

M.

 

[ahaque]

So, are you saying that any date greater than 31/05/2011 is wrong?

If so try this (please, use a test-workbook)

Filter mannualy column F of Combine Sheet to show only dates greater than 31/05/2011 and try the macro below

Sub test()
    Dim lastRow As Long, rng As Range, aCell As Range
 
    With Sheets("Combine Sheet")
        lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
        Set rng = .Range("F1:F" & lastRow).Offset(1, 0).SpecialCells(xlCellTypeVisible)
        For Each aCell In rng
            aCell.Value = Format(aCell.Value, "dd/mm/yyyy hh:mm")
        Next aCell
    End With
End Sub

HTH

M.

Dear Marcelo Branco
U r Great!!!!
I Checked in two column and its seems working fine. Thanks Thanks and Thanks ........... a lot.....
I have total 6 file like this which i collect from 480 Servers and need to generate one report with some staticts but i was in big problem for the DATE,,, Thanks for ur cooperations.
Simply U R great!!!

Thanks MrExcel Forum and it's all members and modaratores... board members............ ALL

 

[Marcelo Branco]

Dear Marcelo Branco
U r Great!!!!
I Checked in two column and its seems working fine. Thanks Thanks and Thanks ........... a lot.....
I have total 6 file like this which i collect from 480 Servers and need to generate one report with some staticts but i was in big problem for the DATE,,, Thanks for ur cooperations.
Simply U R great!!!

Thanks MrExcel Forum and it's all members and modaratores... board members............ ALL

Tks very much for your kind words.

I'm very happy that the macro worked for you.

All the best

M.