Monthly Archives: January 2021

Excel

Months to years and months

You have a number of months and that number should be converted to years and months.

I have found two ways for this, there must be much more options.

Function INT is obviously an abbreviation of integer. Integer is also a data type in VBA for whole number. For example, INT for 2,2346 equals to 2.

Full years can be defined by dividing the months by 12 and taking the integer of the result.

Another way is again to divide months by 12 and rounding the value down to whole number.

The ROUNDDOWN function needs arguments number and num_digits. If num_digits is 0, the formula returns the rounded value down without decimals, just like INT.

Number of remaining months can simply be calculated by multiplying the years, defined by INT, by 12 and deducting that from total number of months. Like 4 = 16 – 1 * 12.

The other option for remaining months is using the MOD function to count remainder. Arguments for the MOD are just number and divisor. If number is 22 and divisor is 5, then MOD will be 2.

Like this.

It is better to check that the models work correctly with values less than one year.

VBA

Adding a leading number

You have a long list of receipts written down in Excel. The receipts should be numbered. The first one should be 1. Receipt A, second 2. Receipt B and so forth.

This might be the case when you have receipts as hard copies and you need to number the hard copies. Also the Excel numbering must be in sync with numbers in hard copies.

Of course, you can add a column with running number and the combine the two columns.

This is fine, too. Still, you need to create more columns and in real life cases columns F and G might already have been populated.

Another option is to execute the macro below. Place the cursor where the data begins, in D2.

Sub znum()

Dim num

Dim cv

num = 1

Do Until ActiveCell.Value = “”

cv = ActiveCell.Value

ActiveCell.Value = num & “. ” & cv

ActiveCell.Offset(1, 0).Select

num = num + 1

Loop

End Sub

Variable num is increasing by one after every cell. The variable is added in front of the cell context. The cell context is the variable cv. The macro manipulates the cell one by one and stepping down after each cell. When the next empty cell is found, then the macro ends. You cannot have empty lines within the data.

The benefit of the macro is that it is overriding the existing values in D-columns.

Place the cursor in D2 and execute the macro. This is the result.