Help on Previous Add Time

  • Thread starter Thread starter Troy
  • Start date Start date
T

Troy

I still need help on adding a column of cells that have
time formats and whole numbers. I want only the whole
numbers summed up.
 
one way:

=SUMPRODUCT(--INT(A1:A1000))

Note: If a time is >24 hours the integer portion of that value will
be counted as well. For instance: 36:00 is stored in XL as 1.5 so 1
would be included but .5 would not be.
 
I will have times that exceed 24 hours maybe even 100
hours. This there a way I can not included any time
formats. Summing specific cells manually is not an option
becuase there are too many cells.
 
One way:

This will sum cells that are not formatted as times. Non-numeric
values are ignored

Public Function SumNonTimes(rRng As Excel.Range) As Variant
Dim cell As Range
For Each cell In rRng
With cell
If IsNumeric(.Value) Then _
If InStr(.NumberFormat, "h:m") = 0 Then _
SumNonTimes = SumNonTimes + .Value
End With
Next cell
End Function


call as:

=SumNonTimes(A1:A1000)

Note that changing formats within the range will not cause this
function to recalculate.
 
Back
Top