View Full Version : Add value, as seconds, to LongDate

10-22-2012, 12:16 AM

I'm working on some VBA for Excel 2003 currently and am having a bugger of a time with something. I'm trying to add the following:

jCell = "09/24/2012 16:42:02"
eCell = "30" which is to represent 30 seconds.

My original equation is:
AnalysisWorkssheet.Range(dCell).Value = AnalysisWorksheet.Range(jCell).Value + Time(0, 0, AnalysisWorksheet.Range(eCell).Value)

dCell = where I need it to go and in this example it should be "09/24/2012 16:42:32"

I'm getting a <Type Mismatch> on the Time() portion, however when in debug mode, when I hover over the 'eCell' portion of the equation it IS showing the desired value that I would like added to "jCell".

Anyone have any thoughts? I've been scouring the internet for several days on this one and have been mainly fruitless....


Any thoughts or suggestions?

10-22-2012, 01:22 AM
With AnalysisWorkssheet

.Range(dCell).Value = .Range(jCell).Value + TimeSerial(0, 0, .Range(eCell).Value)
End With

10-22-2012, 07:36 PM

Thanks for the pointer, but I'm still not getting the desired results.

Updated Code (disregard If/End If statement):If AnalysisWorksheet.Range(jCell).Value <> AnalysisWorksheet.Range(NextJCell).Value Then
ShowStartTime = AnalysisWorksheet.Range(jCell).Value
AdToStartTime = TimeSerial(0, 0, AnalysisWorksheet.Range(eCell).Value)
AdStartTime = ShowStartTime + AdToStartTime
AnalysisWorkssheet.Range(dCell).Value = AdStartTime
End If

1. The math is correct, however, when attempted to set the value of dCell to be equal to AdStartTime, I now receive an "Object required" error. Debug mode shows the value as correct; albiet, it's not a string, just a Date. Is there a special process that one needs in order to set the value of a cell to this new variable value?

2. Despite the math being correct, the time is being returned in 12hr format; for this project, it must be in 24hr format, no AM/PM distinction. Can this be done?


Thanks again! :D

10-22-2012, 07:58 PM


AnalysisWorkssheet.Range(dCell).Value = AdStartTime
^ extra 's'

Dur.... hehehehhehehe :banghead: :banghead: :banghead:

10-23-2012, 12:50 AM
If you had used With ... End With as I showed that would have been avoided/obvious.