PDA

View Full Version : Excel VBA code to Correct Day ?



gunny2k9
01-13-2012, 08:50 AM
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
[/URL][URL="https://www.rapidshare.com/#%21download%7C33%7C1325602612%7CMidnightCode.zip%7C5"]https://www.rapidshare.com/#!download|33|1325602612|MidnightCode.zip|5 (https://www.rapidshare.com/#%21download%7C33%7C1325602612%7CMidnightCode.zip%7C5)

Bob Phillips
01-13-2012, 10:29 AM
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

gunny2k9
01-13-2012, 01:01 PM
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
01-14-2012, 09:02 AM
Not sure what you mean, as that looks like a date field to me.

Simon Lloyd
01-14-2012, 09:36 AM
...also, if the date field is missing there's nothing to correct :)