Apply part of invoice to different object codes

Haley Gehris

Hello, I am trying to write a formula to give me the amount of an invoice I need to apply to 2 different object codes. GL code 6022 needs to be used for payments from $100,000-$125,000 and the other GL code 6021 is used for everything else. I have a current invoice column and a historical invoice column. So for example:

Current invoice is: $120,000

Historically invoiced: $80,000

So I need to pay out $40k, but the first $20k needs to be applied to the GL code 6021 and the second $20k needs to be applied to the GL code 6022.

I'd like to create 2 new columns, 1 for the 6022 GL and one for the 6021 GL and have a formula calculating the amount of the invoice to apply to each. TIA for your help with this!

Kelly Moore

Hey @Haley Gehris

Try this

GL code 6022

=IF(AND([Current invoice]@row >= 100000, [Current invoice]@row <= 125000), IF([Current invoice]@row > [Historically invoiced]@row, IF([Current invoice]@row - [Historically invoiced]@row > [Current invoice]@row - 100000, ([Current invoice]@row - [Historically invoiced]@row) - ([Current invoice]@row - 100000), [Current invoice]@row - [Historically invoiced]@row)))

GL code 6021

=IF(AND([Current invoice]@row >= 100000, [Current invoice]@row <= 125000, [Current invoice]@row > [Historically invoiced]@row), ([Current invoice]@row - [Historically invoiced]@row) - [GL code 6021]@row, IF(AND([Current invoice]@row > [Historically invoiced]@row, OR([Current invoice]@row < 100000, [Current invoice]@row > 125000)), [Current invoice]@row - [Historically invoiced]@row))

I wasn't quite sure what to do if the Historically invoiced amount was larger than the Current Invoice. You will see within the IF criteria I check for that, and don't allow a value to show up in either 6021 or 6022.

Will this work for you? If the logic isn't right, shout out to me.

Kelly