Hi,
Use an If condition in F: if E is empty then F = "" else "the formula you already have"
In G, IFERROR return 0.
In you code use the following for F (replace line 49 of module 3):
Code:ActiveCell.FormulaR1C1 = _ "=IF(RC[-1]="""","""",[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]])"
and the following for G (replace line 123 of module 3):
Code:ActiveCell.FormulaR1C1 = "=IFERROR(IF([@SLA]*24<1,1,0),0)"
On a side note, I noticed this sample isn't using some of the things we discussed earlier (the formula for when the issue is resolved in the next day, for instance).
Hi Pcosta87
Thanks a lot my friend, I have rectified that issue by rounding the numbers
, also the if formula works fine to solve the empty cell, now with the issue that
we discussed earlier if the issue is resolved next day, I tried to combine the o2 things together but the error message comes.
Just wondering how do we combine 02 if statements together.
1. ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]])"
2. ActiveCell.FormulaR1C1 = _
"=IF(And(DAY([@[Date and time escalation was resolved]])>DAY([@[Date and time escalation was received]]),[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]]-""15:00:00"",[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]], RC[-1]="""","""",[@[Date and time escalation was resolved]]-[@[Date and time escalation was received]]))"
Other very interesting thing I noticed, if I give this file to someone else
to use it , as long as the data is there and they refresh the pivot table then its fine, but if the SLA sheet is empty and someone accidently refresh the pivot table then pivot table collapse as it cant find the within SLA cell and then it shatters and then I have to edit the pivot table again which is a pain. is it possible when the SLA sheet is empty then nobody can refresh the pivot table.
thanks
Tauqeer