Formula for getting the last day of the month

…a quick tip that you may find useful in your applications…

I have spreadsheets that produce journal entries that I can then import into the general ledger. I reuse these spreadsheets every month.  I always make the effective date the last day of the month. To make it easy on myself (why not), I just enter the first day of the month in the cell above, and use this handy formula:

Example:

Cell A1:
6/1/2017
Cell A2:
=(DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))-1
Results of formula
6/30/2017
How it works

It takes the date (6/1/2017), then makes it the first day of the next month (7/1/2017), then subtracts one day, making it 6/30/2017.

Creating Debits and Credits from Net

You might have a trial balance that just has net amounts that you need in separate debit/credit columns. See example:

In Col E we will create our Debits, and Col F our Credits. We’ll leave Col D blank to give some separation.

In Cell E2 insert the formula:

=IF($C2<0,0,$C2)

In Cell F2:

=IF($C2<0,-$C2,0)

Now, copy the formulas down for the whole TB. The results should  look like:

I have added a simple formula in Cell F9 to make sure that my debits and credits equal the original list. It does!

 

VLookups – Add missing information to your general ledger

VLookups are valuable when you have data in two places, but need to link the two.

An example in our general ledger might be when you have to report by some alternate account numbers. In the government accounting world, you are sometimes asked to report to a funding agency using their prescribed numbers.

In our sample GL data:

To the right of our example (Column D), we want to put our new account number. In a separate tab named “New Accounts”, we will have all of our account numbers with their related new account numbers. See the layout:

Back in the tab with our GL data, in cell D2, type “=VLOOKUP(A2,’New Accounts’!A:D,3,FALSE)“. Let’s look at the pieces of the formula arguments:

Arguments explained:

  1. Lookup_value – This is the common part of both lists of data. In our case, it’s our account number.
  2. Table_array – This is where our new data is located. For us, it’s the tab “New Accounts”, and we have columns A through D (or A:D).
  3. Col_index_num – We have 3 here because we are choosing the third column of data from New Accounts. This is where our new account number is located.
  4. Range_lookup – I always put “FALSE” here. This means it must find an exact match in the new data.

I have copied the formula all the way down, here is the final product:

The next step might be grabbing the New Account Description (the forth column in our lookup data). In cell E2, that formula would just be “=VLOOKUP(A2,’New Accounts’!A:D,4,FALSE)“. The only difference in our formula above is the “4“, instead of “3“.

Once you have connected your existing GL data with the new account numbers, you could do some pivot tables to create income statements, balance sheets, etc.

One very important thing to know about this formula is the “Lookup_value” must have the common value (our account number) as the first field in the data we are connecting. This can sometimes require us to move our columns around. 

 

Breaking apart account strings – Formula addition

Using the same example from Text to Columns.

Instead, we will use formulas to break out the different segments of the account string.

Assuming our string is in cell A2. Amount is in B2. We will put our first formula in C2.

The formula we will use is “=MID(A2,1,3)“.  The function’s arguments are in three parts. First is the text (A2). Then the starting number, which is 1, then the number of characters. We want three characters because that’s how long our fist segment is.

The result is “100”, which is our first segment. We could do this again to get the next segment. In cell D2, enter “=MID(A2,5,6)“.  The difference is we are starting at the fifth position, and we a selecting six digits.

The result is “401000”- our second segment. We could do this for the remaining three parts of the account string. So, why would we choose to do this vs Text to Column? Well, if your data is static, Text to Column is probably the best. But, you may be working with a query from a database that can be updated. In that case, formulas would be the best route.

 

Converting formulas to values

In many cases when working with your general ledger, it becomes necessary to move data around that have formulas attached. Sometimes, those get broken in the process. In order to “lock” in those formulas values, they must be hard coded. A easy way to do this is by using the copy/paste special feature in Excel.

To do this, select the cells with the formula

  • Right click
  • Copy
  • Paste special

Just that easy!

And an even faster way of doing this is to create a macro in Visual Basic.

Sub Copy_PasteSpecial()
 Selection.Copy
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 Application.CutCopyMode = False
End Sub

Power of SumIf’s (Part 3) – Wild cards

Let’s say that you have list of account numbers and amounts.

Let’s assume that all the accounts that start with 4190 are “Admin Expenses”, and all the accounts that start with 4570 are “Maint. Supplies”. There is a way to summarize these with the sumif formula, and wild cards. Here’s how.

Below our list add 4190??, 4570??, and 4580??. The question mark character is our wild card.

In the C column…to the right of our category descriptions, enter the formula “=SUMIF($A$2:$A$17,A20,$C$2:$C17)

Remember to use the $ in the formula so that when you copy it, it keeps the ranges intact.

The final result…with total added to match our total above.

 

Convert transaction posting date to month

When your transaction data comes out of the general ledger it may be in a pretty raw format. You might get something called a posting date (in a format like 06/30/2016). Let’s say you want to work with the data in a pivot table where you have a column for month.

The month can be easily pulled out this this formula.

06/30/2016 is in cell A1 for example. in A2 you put “=month(A1)”. The result returns 6. This can also be used with day and year too.

6/30/2016 =MONTH(A1) 6
7/1/2016 =DAY(A2) 1
7/2/2016 =YEAR(A3) 2016

Thanks!

Converting GL transaction data – populating lines with account numbers

Let’s say you export out transaction detail from the general ledger. You may want to work with it in a pivot table. But the individual lines are missing the account numbers. They might be above or below the data. Here is a trick to populate the lines.

Here is a sample of our data. Highlighted in yellow is where we want our account numbers.

In cell A3 type “=A2”. Now we want to copy this formula but only in the yellow cells. Not on top of the remaining header account numbers.

  • Copy cell A3.
  • Select the range where you want to paste.
  • Press F5 to launch the GoTo box.
  • Click the “Special…”
  • Click “Blanks”
  • Now you selected just the blank cells.
  • Ctrl V (or paste from the menu).

I’ve changed the area from yellow to blue to show how it should look with account numbers populated.

Power of SumIf’s (Part 2)

In our first post, we looked at the power of SumIf’s. Here, we will look at another way to summarize the same data, but by location code.

We add our three location codes from the data above in Col. A. In Col. B the formula “=SUMIF($A$2:$A$37,A41,$B$2:$B$37)“.  In this example, cell A41 has our location code “001”. Copy/paste the formula.  We add a total to check against the sum of all data.