Monthly Archives: December 2018

Functions

Defining age based on birth date

A list of birthdays is reported. Persons should be divided into two groups. First group consists of persons under 21 years of age. Persons 21 or above belong to the second group.

First we need to define what is the birthday for those people who are exactly 21 years of age. Anyone whose birthday is later, belongs to group “under 21”. Who was born exactly 21 years ago or earlier belongs to “21 or older”.

Function =TODAY() returns the current date. Function DATE consists of three parts: year, month, and day. Number category for the cell E2 is date.

Here is a simple example about DATE function.

Date today is Dec 30th 2018. The border line is the date 21 years ago, the date can be calculated with DATE function. Year in DATE function is current year minus 21, the month is current month and day is current day. If the birthday of a person is exactly border line or earlier then the person is 21 or older. If person is born later than border line, then the person is under 21.

The values in cells B4:B24 are random values. You can enter values manually to test the solution. However, if you want to limit your manual work, you can use RANDBETWEEN formula. Using DATE nested formula inside RANDBETWEEN you can define lower and upper limit for random values.

The formulas are here for copy and paste:

=DATE(YEAR(F2)-21;MONTH(F2);DAY(F2))

=IF($I$2>=B4;”21 or above”;”Under 21″)

=RANDBETWEEN(DATE(1960;1;1);DATE(2015;1;1))

Another solution is to use DATEDIF function.

DATEDÍF requires earlier date, later date and type and reporting unit. B4 cell holds the birthday, TODAY() is the current date, y means that value is returned in years.

When you set DATEDIF inside IF function, function returns you the wanted result.

Excel

Counting account balance

When you make accounting postings for an account and after the period, you want to check the balance of an account. You need to count both debit and credit side. If sum for debit side postings is higher than sum of credit, then you need to add the difference to credit side to match both debit and credit sides. In this case the account has debit balance.

The formulas presented are in cells F16 and G16. If you want to copy paste the if sentences, they are here:

=IF(SUM(G4:G14)>SUM(F4:F14);SUM(G4:G14)-SUM(F4:F14);””)

=IF(SUM(F4:F14)>SUM(G4:G14);SUM(F4:F14)-SUM(G4:G14);””)

If formula in debit side checks first if sum for credit postings is higher than sum of debit postings, in this case, the difference between credit and debit is counted. If the if sentence is not true, sum of debit postings is higher than credit postings, then the cell should be blank, as in that case we have credit balance.

VBA

Delete empty rows in Excel

When downloading data from ERP to Excel, every second row in Excel is populated and every other is blank.

The Excel would look something like this.

Before you execute the macro, write “end” in the A column cell after the data area.

Like this.

The macro is here:

Sub deleteemptyrows()
Range(“a1”).Select

Do Until ActiveCell = “end”
If ActiveCell.Value = “” Then
ActiveCell.Rows(“1:1”).EntireRow.Select
Selection.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Select
End If

Loop

MsgBox "Macro done", vbOKOnly, "Own macro"

End Sub

The macro takes the cursor to cell A1. Then the macro checks every cell in A column, if the cell is empty then the row is deleted. If the cell is populated, then the macro jumps to the next downwards in A column.

The result should be this: