Help on Previous Add Time

[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.

 

[J.E. McGimpsey]

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.

 

[Troy]

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.

 

[J.E. McGimpsey]

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.

 

[Troy]

Now you got me confused. Am I to make a new function? What
menu do I go to do this?

 

[J.E. McGimpsey]

Yes. It's called a User Defined Function, or UDF.

Take a look here:

http://www.mvps.org/dmcritchie/excel/getstarted.htm