Insert Outcome of Formula as Text Rather than Formula Itself

[Philippe.T]

Hello all,

The following code inserts formulas. the outcome are times [like: 06:00:00 AM, 12:30:00 PM etc]
I'd rather have the outcome as text [instead of the formula] like 06:00, 12:30 etc.

Any ideas?

Set MyRange2 = Application.Union(Range("D8:FO8"), Range("D11:FO11"), Range("D14:FO14"), Range("D17:FO17"), Range("D20:FO20"), Range("D23:FO23"), Range("D26:FO26"), Range("D29:FO29"), Range("D32:FO32"), Range("D35:FO35"), Range("D38:FO38"), Range("D41:FO41"), Range("D44:FO44"), Range("D47:FO47"), Range("D50:FO50"), Range("D53:FO53"))
MyRange2.FormulaR1C1 = "=IF(AND(R[1]C[1]=TRUE,R[1]C=FALSE),SUMPRODUCT((Data!R7C3:R125C3=R[1]C3)*((Data!R7C7:R125C7=INT(R7C[1]))*((HOUR(Data!R7C8:R125C8)<=HOUR(R7C[1]))*(Data!R7C8:R125C8)))),IF(AND(R[1]C[-1]=TRUE,R[1]C=FALSE),SUMPRODUCT((Data!R7C3:R125C3=R[1]C3)*((Data!R7C13:R125C13=INT(R7C))*((HOUR(Data!R7C14:R125C14)<=HOUR(R7C))*(Data!R7C14:R125C14)))),0))"

 

[VoG]

Try

Set myrange2 = Application.Union(Range("D8:FO8"), Range("D11:FO11"), Range("D14:FO14"), Range("D17:FO17"), Range("D20:FO20"), Range("D23:FO23"), Range("D26:FO26"), Range("D29:FO29"), Range("D32:FO32"), Range("D35:FO35"), Range("D38:FO38"), Range("D41:FO41"), Range("D44:FO44"), Range("D47:FO47"), Range("D50:FO50"), Range("D53:FO53"))
myrange2.FormulaR1C1 = "=IF(AND(R[1]C[1]=TRUE,R[1]C=FALSE),SUMPRODUCT((Data!R7C3:R125C3=R[1]C3)*((Data!R7C7:R125C7=INT(R7C[1]))*((HOUR(Data!R7C8:R125C8)<=HOUR(R7C[1]))*(Data!R7C8:R125C8)))),IF(AND(R[1]C[-1]=TRUE,R[1]C=FALSE),SUMPRODUCT((Data!R7C3:R125C3=R[1]C3)*((Data!R7C13:R125C13=INT(R7C))*((HOUR(Data!R7C14:R125C14)<=HOUR(R7C))*(Data!R7C14:R125C14)))),0))"
myrange2.Value = myrange2.Value

 

[Philippe.T]

Hello Peter, thanks for trying, but nothing changed with your code

 

[texasalynn]

try wrapping the formula with Evaluate(..your formula here)

 

[Philippe.T]

Hello Alynn, that one resulted in #value.
Is it perhaps possible to change time to text [afterwards]?