Excel VBA code to Correct Day ?

[gunny2k9]

Hi,

we have a system that generates a report and a tool to manipulate the report to a easy reading format however the system that makes the reports is not great and causes some issues,

i need to vba code to run on a column to correct days just wondering if theres code out there before i try re invent the wheel

eg the roport gives us:

DATETIMEDAY16/01/201223:54Mon16/01/201223:58Mon17/01/201200:03Mon17/01/201200:08Mon

but i need code to check that and notice that it runs over midnight and corrects it to:

DATETIMEDAY16/01/201223:54Mon16/01/201223:58Mon17/01/201200:03Tue17/01/201200:08Tue

any suggestions welcomed ?

EDIT tables doent seem to work i nthis board i ll try upload a sample file in a little bit
https://www.rapidshare.com/#!download|33|1325602612|MidnightCode.zip|5

[Bob Phillips]

[vba]
Application.ScreenUpdating = False

With Activesheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow

.Cells(i, "B").Value = Format(.Cells(i, "A").Value, "ddd")
Next i
End With
[/vba]

[gunny2k9]

thanks xld that works,

now i dont mean to be a pain in the butt here, but in some reports there is no date feild of eg 16/01/2012 dont suppose you know a ay to work it out with out that field ?

if not not to worry i m sure i can figure something

[Bob Phillips]

Not sure what you mean, as that looks like a date field to me.

[Simon Lloyd]

...also, if the date field is missing there's nothing to correct