7 of the coolest but simple Excel functions that can help an HR to do smart work at office
Do you know that it’s been long 33 years since Microsoft Excel was first launched? Today, in spite of having various alternatives for analyzing, manipulating and visualizing data we still use Microsoft Excel for most of our office tasks. That’s because Excel comes with loads of powerful features, formulas and chart gallery that makes it a very useful tool for our daily office jobs leaving behind all the other alternative tools available in the market.
The HR professionals who work for Recruitments, training & development, payrolls, compensation & benefits, employee relations, etc. have all come across Excel sheets. For those of whom, who are not very good at excel or are beginning to learn this tool can get intrigued by how some of your colleagues do task in hours, which you would otherwise take days to complete. A survey revealed 38% of office workers’ time is spent using Excel.
What follows is the first function of our 7 essential but simple Excel functions that can help any HR or desk job professionals to do their tasks quickly and stand out from the rest of the herd.
Excel Date & Time functions
One of Excel’s most powerful features is its ability to handle dates and time. As we work with dates, we would be always interested to know how much time has elapsed between certain days. Sometimes, it is the question of actual days elapsed and other times its about the time in hours, minutes or even seconds. Excel can handle nearly all of those situations beautifully.
Let’s see an example below:
Suppose we need to track the number of days an equipment was used and what we have is the starting date and ending date. We need to know how to calculate the number of days this machine was operational. First step is just to enter the starting date and ending date as shown below in cell B1 & cell B2:
The number of days this machine is operational is calculated by subtracting cell B2 with cell B1, i.e B2-B1 as shown below:
Thus, the number of days the machine was operational is 366 days. Wasn’t is it very simple but useful tip!!
Let’s see another example with date function:
Suppose an organization hires a contract emplyoee for a period of 6 months (180 days) and his contract starts from 1st May, 2020. We need to know when his contract will end.
For this, the first step is just to enter the contract start date and duration of contract as shown below in cell B7 & cell B8:
The contract end date is calculated by adding cell B7 with cell B8, i.e B7+B8 as shown below:
Thus, the employee’s contract ends on 28th October, 2020.
Current date & current time
When you are working with dates and time, it’s not uncommon to want to have the current date and current time. There are two different ways to use these capabilities.
If you simply want today’s date and time, you don’t have to type anything. Just use the keyboard shortcut for Date as ( Ctrl + ; ) and for Time as ( Ctrl + Shift + ; ). The screen grabs below will show the date and time when I was creating this post.
Now, if you save the file and open this file again, those days and time are going to remain the same. However, sometimes you would need those date and time to be like a moving target. To have the current date showing up always, simply put =today( and Enter. For this case, the date using the shortcut key and this formula will look alike. But if you save the file and reopen it tomorrow, the date using the formula will show tomorrow’s date which will be same as that showing in your system clock. You can have a check yourself!
Similarly, for current time to be showing up always, simply put =now( and Enter. This will give both date and time.
But if you only want to see the time and no date, then you can use the Format Cells option and use the settings as shown below:
Stay tuned, as this is just the first part of our 7 Essential Excel functions for HR professionals and we will be back soon with the remaining ones. Between, if you have any queries or want some particular excel functions to be explained, kindly let us know and we would put it live in our upcoming posts.
