Category Archives: Formulas

Date_Math_8

Using Date Math in Excel to Calculate Payday Dates

Date_Math_8It’s the time of year that young, energetic college students are being interviewed for  summer internships. Today we selected our first summer hire and began the seemingly straightforward process of preparing the employment paperwork. The full packet grows heavier each year with the employee handbook, policy statements, a multitude of governmental forms, emergency contact form, and so on.

There is one part of the process that I find more than a little bothersome. The employment letter specifies the first day of employment and explains to the new employee the bi-weekly pay periods and the date that the first paycheck will be issued. Usually I pull up an electronic or printed calendar and go through a lot of manual gyrations, circling the paydays and counting back through the paycycle. Today I decided to spend a bit of time in Excel to automate this process.

Date Math becomes a “Calendar Calculator”

Date math refers to several functions in Excel which return the number of days between two dates or allows days to be added to or subtracted from a particular date. Our payroll is on a bi-weekly cycle with a one week delay. Each pay period begins on a Sunday and ends on a Saturday. Paychecks are issued on Friday. Using the month of March, if the pay period  starts on Sunday, March 2, it will end 14 days later on March 15. The processing will occur in the following week and the paycheck for that period will be issued on Friday, March 21.

Date_Math_4

My objective is to enter a new employee’s start day and have formulas calculate his or her first payday, the first date of the pay period, and the last date of the pay period. To begin I pick a random payday in the past – January 11, 2013. We’ll name this ORIG_PAYDAY in the formula. It could have been any prior payday as all future paydays will always be some multiple of 14 from any prior payday. Next the employee’s start date (START_DATE) is entered in Cell B2.

Date_Math_9

The formula build begins with determining the number of pay periods between the ORIG_PAYDAY and the START_DATE.

=INT((START_DATE – ORIG_PAYDAY)/14).

The INT() function rounds the result down to the whole integer, so 30.2142 becomes 30. Thus, the new employee’s first payday will be either the 31st or 32nd payday after our arbitrary ORIG_PAYDAY.

Since it will be infrequent that a start date will fall precisely on the day of a new pay period, we’ll also need the remainder, i.e., the number of extra days beyond the 30 pay periods. That function is MOD((START_DATE – ORIG_PAYDAY),14), yielding in this case: 3. The remainder for each possible start date is shown in red below:

Date_Math_3

Note that all of the possible start dates with a remainder of 8 or less will be within the 2nd week of the pay period and will be paid on March 21, the 31st pay period. However, any start date with a remainder of 9 or more will fall in the processing week which is actually the 1st week of the next pay period and will be paid on April 4, the 32nd pay period.

Putting it altogether using date math, we add to the original payday the product of  pay periods between the start date and original payday and plus one times 14 days.

Date_Math_5

The final calculations are easy-peasy. Subtracting 19 from the first payday returns the first day of the pay period and subtracting 6 returns the last day of the period.

Date_Math_6

Breaking long formulas down into their component parts can help make them less intimidating. Creating this formula by building those components one at a time took a good thirty minutes, a fair amount longer than my usual manual calendar counting. However, now I have the worksheet saved in the same folder in which I store the employment letter templates and in the future, it will take all of 15 seconds to open the file, type in the employee’s start date, and receive all three relevant dates.

If you found this post helpful, you may subscribe to receive more great tips, tricks, and templates by email.

Cheers,

Annie

Custom_Function_1

How to Create a Custom Function in Excel

User_Defined_1I’ve always been intrigued by people who are outgoing enough to “flower power” their vehicles. While secretly wishing to have that sort of bravado, I have to admit to a shyer personality that could never handle such an attention-getting customization.

On the other hand, customizing Excel to match my preferences is right up my alley! In two previous posts, I illustrated how personal format preferences could be set as the default for new spreadsheets (here) and how the auto-formatting of hyperlinks could be removed (here). Today’s post will create a user defined or custom function for a calculation that is not available in Excel’s built-in functions.

A Custom Function to Calculate Percentage Change

It’s always been a mystery to me why there is not a built-in function to calculate percentage change between two values. Although it is simple to calculate, I use it often enough that creating a function could save a lot of time. We’ll use the following data to calculate the percentage change in the number of units sold from one month to the next.

Custom_Function_2

To manually calculate the percentage change from January to February, in Cell C3, we’ll provide =(B3-B2)/B2. Essentially the original number divided by the net change in the original and new value yields the percentage change, in this case, 3.6%.

Custom_Function_3

To transform this formula into a custom function, we’ll need to use the Visual Basic Editor.

1. Press Alt + F11. Select Insert on the Menu Bar. Select Module.

Custom_Function_4

2. Enter the following code. Note that the name of the custom function and the two parameters – PerChange, Older, and Newer were chosen by me on the fly. My first choice was Old and New for the parameters but New was a reserved keyword, so I couldn’t use it.

Custom_Function_5

3. Press ALT + F11 to return to the spreadsheet. The function may now be used in Cell C3 to calculate  the percentage change by identifying the Older and Newer Values in Cells B3 and B2, respectively.

Custom_Function_6

Custom_Function_7

Saving the Function to Use in Other Workbooks

To save this custom function (and others!) to be available in other workbooks, they will need to be saved in a file that is stored in the XLSTART folder.

1. Search for the location of the XLSTART folder in Programs file hierarchy. Using Windows Explorer, search for XLSTART kind:=folder. Make note of the location.

2. Click on File, Save As. Provide a name in the File Name. Select Excel Add-in in the Save as Type drop down options. Navigate to the XLSTART folder located in step 1. Click the Save button.

When Excel starts, the Add-in will be resident in memory and accessible to all workbooks.

If you found this post helpful, you may subscribe by email to receive more great tips, tricks, and templates in your inbox.

Cheers,

Annie

Celebrating PI Day in Style with Excel

One of the radio stations I enjoy on my drive to work has a segment each day to announce the National {___} Day. Some represent historical facts, such as National Girl Scout Day on March 12 commemorating the first troop meeting on March 12, 1912. Others draw awareness to a serious issue, such as National Human Trafficking Awareness Day (January 11). Then there are the downright silly, e.g., National Step in a Puddle and Splash your Friends Day (also January 11).

I missed a big one yesterday. Did you celebrate National Pi Day on March 14? Pi (Greek letter “π”) is the mathematical constant 3.14159 which is the ratio of a circle’s diameter to its circumference. March 14 is chosen to celebrate this mathematical concept because this infinite number begins with the first three digits, 3 1 4. Check out the fun website of piday.org for ideas to celebrate Pi Day, such as having friends over to watch the movie Life of Pi.

PI_DAY_Excel_1
Image courtesy of piday.org.

Using Pi in Excel

Before we get too off-topic, you may be happy to know that if you last used Pi in high school geometry, Excel stands ready to take care of the Pi calculation for you. The PI() function in Excel will provide the constant in calculations. Recently I was covering a piece of furniture that had a circular top. I knew the top was 24 inches in diameter, but I needed to know how long a trim piece would need to be to go around the outside edge or circumference. The formula =B1*PI() provides just the information I needed — 75.4 inches!

PI_DAY_Excel_4

PI_DAY_Excel_5

One More Reason to Celebrate Pi Day

The piday.org website shared one particular tradition that I will make part of my future PI Day celebrations. Using those first three digits, 314, look what happens when we take a mirror image:

PI_DAY_Excel_2PI_DAY_Excel_3

Won’t you join me for a piece of PIE to celebrate PI Day?

Annie

 

Connecting Data Sets using VLOOKUP in Excel

In my dream world, the data management systems I use would allow me to extract data in the exact sequence and combinations I need. More often, I am able to extract only certain segments of data and then have to build bridges to connect those data sets outside of the original source.

Within the same or multiple workbooks, different data sets may be brought together using the VLOOKUP function. In this example, we reminisce to the lazy, hazy days of attending summer camp as a kid. Campers Becky, Joel, Vicky, Julia, and Anthony have signed up for various activities each day of the week. Their choices have been entered in the data management system as Activity Codes and are listed on the spreadsheet named Campers.

VLOOKUP_Excel_1

VLOOKUP_Excel_3In order to create a schedule for each camper for the week, the translation of the codes to the Activity name and Location will be accomplished using VLOOKUP. In a second sheet named Activities, the data is setup such that the Activity Code is in the first column of the data range. The Activity Code fulfills the first requirement in using VLOOKUP in that this is the common ID or key that exists in the each dataset. The second requirement is that the Lookup table is sorted by this ID or key.

VLOOKUP_Excel_2

To connect these two sets of data, the VLOOKUP formula will be entered in the Campers spreadsheet cell D2.

 

VLOOKUP_Excel_4

The first parameter needed to identify Becky’s Monday Activity is the Activity Code found in cell C2.

The next parameter is the address of the data containing the name and location of the date — the absolute reference Activities!$A$1:$D$6.

The next value is the column number of our target data, Activity in the 2nd column.

The last value in the VLOOKUP syntax will be FALSE. FALSE is used when an exact match is required. The alternative TRUE would be used if an approximate match was desired. I use FALSE almost 100% of the time. In fact, I’m not sure if I’ve ever wanted an approximate match and chose TRUE.

Using almost all of the same values, the Location may be filled by changing only the third parameter in the VLOOKUP formula. In cell E2 on the Campers sheet, we’ll type =VLOOKUP(C2, Activities!$A$1:$D$6,4,False). This will lookup on the same row of code AL001 in the Activities spreadsheet but will return the value in column 4 — Pool 1.

VLOOKUP_Excel_5

Copying the formulas to connect the data sets for all remaining rows will enable schedules to be prepared for all campers. Let the fun begin!

VLOOKUP_Excel_6

If you found this post helpful, you may subscribe to receive more great tips, tricks, and templates by email.