Adding time in HH:SS format.

[USAMax]

I have a spreadsheet that has time listed in hours and minutes.

I need to add these times together but every now and then I get a, "Type Mismatch" error. Here is what the code looks like.

code
Public IndexArray(12, 3) As Double
Public Lvl3Sum As Date

x=3
Format(IndexArray(3, 2), "hh:mm") = Cells(x,3)
Lvl3Sum = Format(Lvl3Sum, "hh:mm") + Format(IndexArray(3, 2), "hh:mm")
code

The code is too long to list but you can see the array type is Double and Lvl3Sum is Date. I am not using a date but I thought this was the best type to use.

 

[par60056]

how can you have a function call to format on the left side of the equals?

 

[USAMax]

Lvl3Sum is a variable that has been declared as a Date type.

 

[par60056]

the line above that says:
Format(IndexArray(3, 2), "hh:mm") = Cells(x,3)

which has the format on the left of the equals

 

[par60056]

I am not sure what you are trying to do. Times are just decimal numbers formatted to look like a time. Even if Excel is displaying hh:mm it is still a decimal number and just be added. FORMAT is useful for converting it to a string to display as part of a longer string

timeMsg="the time is " & Format(now(),"hh:mm")

 

[USAMax]

I am not sure what you are trying to do. Times are just decimal numbers formatted to look like a time. Even if Excel is displaying hh:mm it is still a decimal number and just be added. FORMAT is useful for converting it to a string to display as part of a longer string

timeMsg="the time is " & Format(now(),"hh:mm")

The problem is that I am using this variable to verify that the calculations are correct as I step through the program. Just from looking at the decimal numbers I cannot tell if it is adding together correctly but it is easy to see 00:10 and 00:15 should be 00:25 but if they are decimal I can tell if the two numbers added together correctly but that is all I can tell.

I need to see the value as a time to test the program.

 

[par60056]

dim newTime as string
newTime = format(Lvl3Sum+cells(x,3),"hh:mm")
Lvl3Sum = Lvl3Sum+cells(x,3)