Time is driving me crazy

[Matt S]

Hello everyone!

I have a file that is generated on a second-by-second basis. Unfortunately,
it does not give me a time column, rather a 0,1,2,3,... column. It does give
me the start time, however, in this form:

Fri Jan 23 14:13:19 2009 (GMT-05:00).

My macro picks out the 14:13:19 using the Mid function, but after pasting
this to a column, I want to add one second to it and then extend it down for
the entire data set. I am having trouble adding one second to it. Here's my
macro thus far:


Sub TimeDilemma()

Range("D,F:F,H:H,J:J,L:L,N:N,P,R:R,T:T").Delete Shift:=xlToLeft
'Cleans up my data

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
TimeLength = Len(Range("A6").Value)
StartTime = Mid(Range("A6").Value, TimeLength - 24, 8)

Range("A8").Value = StartTime

'make Range("A9") = StartTime + 1 sec then autofill down?

Range("A8:A9").AutoFill Destination:=Range("A8:A" & LastRow)

End Sub


Any help would be greatly appreciated!

THANKS!

 

[egun]

Excel should be treating the time you past in as a date. If you add one to a
date, you are adding a day:

Jan-01-2009 + 1 = Jan-02-2009

There are 24*60*60 seconds in a day. So, to add just one second, you need
to add a much smaller amount:

TimeNow = StartTime + 1# / 24# / 60# / 60#

Of course, you need to format that column as a time (something like HH:MN:SS)

HTH,

Eric

 

[Matt S]

Thank you egun! Question... why do I need the '#' after the numbers?

 

[JE McGimpsey]

The #'s are type codes - indicating that the number is a Double
precision floating point value.

Of course, you don't need to do all the divisions, you could simply use

TimeNow = StartTIme + 1/86400#

or you can let VBA do the math:

TimeNow = StartTime + TimeSerial(0, 0, 1)