Format Date In VBA

[mouse88]

I have a function that uses the Date data type to return a value which is displayed as a time in my worksheet.

I am using the following to format the time:

SUMIFNOTERROR = Format(ReturnValue, "[hh]:mm:ss")

This chops off the hours and displays ":01:27".

Am I doing something wrong?

 

[Scott Huish]

Return the value to the spreadsheet as a time value and format the cell as desired.

 

[mouse88]

Ive tried this but the hours default to 00 because they go over 99. I have the cell formatted as [h]:mm:ss.

Heres my function:

Public Function SUMIFNOTERROR(ParamArray Values() As Variant) As Variant
Dim Value As Variant
Dim ReturnValue As Date
ReturnValue = TimeValue("00:00:00")
For Each Value In Values
    If Not IsError(Value) Then
        ReturnValue = ReturnValue + Value
    End If
Next Value
SUMIFNOTERROR = TimeValue(ReturnValue)
End Function

 

[Scott Huish]

You can do this without a UDF:

Why are you using ParamArray?

 

[xenou]

I'm not sure why you need a custom function. This works:
=SUM(IF(ISERROR(A1:A10),0,A1:A10))
(this formula must be array-entered by hitting control-shift-enter rather than just enter).

If your hours go over 99 you can't really use an hh:mm:ss format - there's not enough digits (although I could be wrong - haven't actually tried it to see what happens if you have 100 hours, 100 minutes, and 100 seconds). Use a straight decimal representation (i.e., 100.5 hours and so on).

 

[Scott Huish]

Or without CTRL-SHIFT-ENTER:

=SUM(SUMIF(A1:A4,{"<0",">0"}))

Format as [hh]:mm:ss or hh:mm:ss as needed.

 

[mouse88]

Ok well the data has got to be formatted as hh:mm:ss as this how they are generated on the report that im using and my employer wont accept it any differently.

If you use a custom format for the cell and set it as [hh]:mm:ss it will allow the hours to roll over.

I have used the methods suggested above before but the formula is far too long and time consuming. The values I want to sum are not in cells next to or below each other they are in varies different rows so I would have to use the below formula for every cell and add them all up:

=SUM(IF(ISERROR(A1),0,A1)) + =SUM(IF(ISERROR(A25),0,A25))

With my UDF all I have to do is use:

=SUMIFNOTERROR(B2,B3,B5,B8,F7,G12,F19)

I just cant get it to return the correct format

 

[mouse88]

Got this working by returning a double instead of a date value.

Public Function SUMIFNOTERROR(ParamArray Values() As Variant) As Double
Dim Value As Variant
Dim ReturnValue As Double
ReturnValue = 0
For Each Value In Values
    If Not IsError(Value) Then
        ReturnValue = ReturnValue + Value
    End If
Next Value
SUMIFNOTERROR = ReturnValue
End Function

 

[xenou]

[hh]:mm:ss is not a format that can be used with the vba format() function - yes, it works in Excel worksheets (100:00:00 for 100 hours, which is decimal 4.1666667)

Anyone know what the brackets mean in the Excel custom format?

Regards your original post, vba format() returns a string and the format doesn't work so I can see why using a numeric return value fixes it - that's better anyway.

This would be a slightly simplified version of your formula, if you like:

Public Function SUMIFNOTERROR(ParamArray Values() As Variant) As Double
Dim v As Variant

On Error Resume Next
For Each v In Values
    SUMIFNOTERROR = SUMIFNOTERROR + v
Next v

End Function

It is possible to use a range rather than a paramarray (by way of example - I started thinking about how to answer Hotpepper's question and this was what I came up with):

Public Function SUMIFNOTERROR2(arg As Range) As Double
Dim c As Range

On Error Resume Next
For Each c In arg
    SUMIFNOTERROR2 = SUMIFNOTERROR2 + c.Value
Next c

End Function

But the syntax for using the formulas are different:
=SUMIFERROR(A1,A3,A5) <-- your formula with paramarray
=SUMIFERROR2((A1,A3,A5)) <-- version with a range argument instead
The first probably feels more natural and would be better understood for other users, I think, since you don't need the extra parens.

 

[mouse88]

To be honest I was originally trying to do it with a range but I couldnt get it to accept something like A1, B3, C10. I didn't realise using an extra set of brackets would fix that.

Thanks for the help one this all.