Skip to main content
Solved

GL import scenario debit/credit amounts

  • 28 January 2021
  • 2 replies
  • 361 views

I have a csv file that I am using for a GL transaction import scenario. The debit and credit amounts are in one column, debits positive number, credits negative numbers.  How can I get the import scenario to populate the debit and credit fields properly?

thanks, coleen

  1. In excel Sort the column by the amount.
  2. Insert another column next to this column and label it “Original”.
  3. Go to your sorted column with the data, select all negative numbers and copy them to your new column (shown as original column below).  Make sure they are removed from your original column.
  4. Create a new column and label it “Debit”
  5. Create a formula in your Debit column as show =G2*1 (this will reverse the number).

  6. Confirm the column H now has amounts reversed.

  7. Select all of column H, then right-click your mouse and select copy.

  8. In the same culumn H in cell H2, right-click your mouse and select paste special and select values as forumla.  this will get rid of the formula.  You can also now delect column G “Original”.

    This approach fixes the data in excel and usually just import in the Journal Transaction entry but would also work in an import scenario.  

I just found below solution for this, in case anyone else later read this post. 
Used below formula to assign Net value to Receipt or Disbursement based on the value of Net field:
Disbursement=IIf( CDec((Net]) < 0, -CDec((Net] ) , '0' )
Receipt=IIf( CDec((Net]) > 0, CDec((Net]) , '0' )

 

Reply


Terms & ConditionsCookie settings