Blog

Excel IF statement

Posted on by DevPaulC
IF statement is one of the most common programming function. It is also one of the most used ones in Excel. The IF function can be used with one condition, as described below or with multiple conditions evaluated using the AND / OR functions How does the IF function work The IF function works like this. There are two branches. An IF branch and an ELSE branch. As an example you can use this to assign a value if something happens or not. If event happens then assign a value else assign another value. For example: IF Traffic Light...read more

How to calculate age in Excel

Posted on by DevPaulC
To calculate age from date of birth in Excel, you must use one of the two formulas below =(TODAY()-DOB)/365.25=DATEDIF(DOB,TODAY(),"Y")where DOB is the range (cell) that has the Date of Birth value Age calculation can be done if you have a set of data containing the Date of Birth. Excel has two simple ways to do it. How to calculate age in Excel using subtraction Here is some sample data for the first formula (calculation was done on 2019-10-08, that is the value of TODAY()) How to calculate age in Excel using DATEDIF If you're asking yourself what is the...read more

How to calculate work days in Excel

Posted on by DevPaulC
Excel has some powerful functions to use for calculating the number of work days between two dates. Not only you can calculate the number of working days but you can exclude some special company specific holidays as well. Case study In the following case study, the HR department has a list of employees that took vacation and recorded the vacation Vacation Start Date and the Return to work date in a form. The Human Resources department has to calculate how many days were they out, how many vacation days (working days) they took and to take into account the...read more

Excel Formula: Get day name from a date in Excel

Posted on by DevPaulC
Here are a few methods to get a day name (Monday, Tuesday, Wednesday etc) from a date field in Excel. If you prefer video, see the tutorials below. Use Excel TEXT formula The Excel Text formula will extract the day in a specified text type format. Here is how it works. The formula for this is =TEXT(cell,"dddd") Short Day Name for Date An alternative text for this is a short name for the day (Mon, Tue, Wed, etc). To achieve that, use the formula =TEXT(cell,"ddd") Day Month and Year Names for Date The TEXT function can be used for...read more

COUNT functions in Excel

Posted on by DevPaulC
There are a number of COUNT functions in Excel that you can use to count your records. Here are the most used ones: COUNT - count the number of cells in a range that contain numbers COUNTA - counts the number of cells in a range that are not empty COUNTBLANK - counts the number of cells in a range that are empty COUNTIF - counts the number of cells in a range that meet a condition COUNTIFS - counts the number of cells in a range that meet a set of conditions Let's get into the details COUNT...read more

Convert cm to inches

Posted on by DevPaulC
Build in a smart converter from cm to inches and back. There are two simple methods in Excel to build this. The methods below also wok in Google Sheets. 1 cm = 0.3937 inch and 1 inch = 2.54 cm Simple Calculation. To convert from inches to centimeters, multiply the value in inches with 2.54 or to convert from centimeters to inches, divide the centimeters value with 2.54 to get the value in inches. Using a formula Use the CONVERT formula to convert from any unit to any other unit. and vice-versa There it is. A simple converter, two...read more

Automatically refresh pivot table

Posted on by DevPaulC
You may need to refresh an Excel displayed dashboard or pivot table periodically if your data is coming from an external source (database, web or other). This tutorial will show you how to refresh your pivot table automatically. Here is what you will learn Refresh Automatically when you open the file Refresh Pivot Table every X minutes Refresh on external data change Refresh Automatically when you open the file To setup your pivot to refresh as soon as you open the file, right click on the pivot table and click on "Pivot Table Options". In the Data Tab, tun...read more

Cells and Ranges

Posted on by DevPaulC
Cell In Excel, one grid box is called a cell. A cell is named as the intersection of the column and row. In the example below, the Cell is named C4 because it is at the intersection of row 4 and column C. You can see the name of the cell that you currently have selected in the name field (highlighted in yellow) Range A Range is a collection of two or more cells. If you select a cell and then with the left mouse button pressed, drag the mouse down and right, you would select a range of...read more

How to copy files from WD My Cloud to the attached USB Hard Drive – the fastest way

Posted on by DevPaulC
I had a personal WD My Cloud for a while working as a NAS and I recently added a second Hard Drive for redundancy. I wanted to make sure my data is stored in 2 places in case one of the drives fails. When I connected this second Hard Drive, I mapped the new Shares onto my computer and started to transfer files. It takes FOREVER. So I researched for the fastest method to do that without keeping my computer occupied with this task. And here it is. How to copy files from WD My Cloud to USB drive...read more