Embedded IF statement placements

[cdnqte]

Hi all!

I am writing a report that is calculating deferred/accrued revenue to be accounted for each month. To determine how many days are allowed for deferral I have a column that tells me how many days are in each period. There are 12 buckets as there are 12 billing periods.

To look up to that information in my report I have a formula that says =IF([@[Billing Cycle ]]="LT",SUM(MONTH([@[Last Bill date]])+1),IF([@[Billing Cycle ]]="LD",SUM(MONTH([@[Last Bill date]]+1)),MONTH([@[Last Bill date]]))) which is all good UNLESSS the last bill date is December because if it is it is trying to make it 13 when I only have 12 periods a year.

I tried adding another if statement that says if(month@[Next Bill Date]=12,1,IF([@[Billing Cycle ]]="LT",SUM(MONTH([@[Last Bill date]])+1),IF([@[Billing Cycle ]]="LD",SUM(MONTH([@[Last Bill date]]+1)),MONTH([@[Last Bill date]])))

I must be putting it in the wrong spot as it is not working. All the embedded ifs trip me up every time! If someone could help that would be greatly appreciated!

 

[Dryver14]

what happens if it is December? does it create an error if so could you not wrap the formula in an if error statement?

 

[cdnqte]

I ended up figuring it out, I had it in the wrong spot. This format did the trick:

=IF(MONTH([Last Bill date])=12,1,(IF([@[Billing Cycle ]]="LT",SUM(MONTH([@[Last Bill date]])+1),IF([@[Billing Cycle ]]="LD",SUM(MONTH([@[Last Bill date]]+1)),MONTH([@[Last Bill date]])))))

 

[RoryA]

Assuming the +1 issue only arises for LT cycles, I think this should work:

=IF([@[Billing Cycle ]]="LT",MOD(MONTH([@[Last Bill date]]),12)+1,IF([@[Billing Cycle ]]="LD",MONTH([@[Last Bill date]]+1),MONTH([@[Last Bill date]])))

 

[MartyS]

=IF(MONTH([@[Last Bill Date]])+IF(OR([@[Billing Cycle]]="LT",[@[Billing Cycle]]="LD"),1,0)>12,1,MONTH([@[Last Bill Date]])+IF(OR([@[Billing Cycle]]="LT",[@[Billing Cycle]]="LD"),1,0))

 

[RoryA]

In the original formula, the calculations for LT and LD are different, but I don't know if that was deliberate or not.