Archive for March, 2012

You can perform a simple calculation (division, multiplication, addition, or subtraction) on a list of numbers without the need to write formulas. Is this possible?!

With Paste Special tool in Excel, you can copy-paste any value to one or more values using a mathematical operation. Hence, the outcome in the cell is the result of the operation.

In this list of packages, the weights are all in LB. You just need to convert each package’s weight to KG. Well, there are many techniques to do that, however, to me, the simplest one is by following these steps:

Write the conversion factor (LB to KG, 0.4536) somewhere in the sheet.

Copy the value you typed, select the range of values you need to convert, and then right-click to select Paste Special.

In the ‘Operation’ group, select ‘Multiply’ (as you need to multiply each package’s weight value by the conversion factor to convert it into KG). Hit OK

You’re done. The weights are all converted into KG without the need to write any formula on the sheet, and without using extra columns to perform the calculation process.

Suppose that you manage a set of suppliers’ agreements which start and expire on specific dates. You need to be warned on the expiry of any agreement, say, 10 days ahead of the deadline. How can you use Microsoft Excel to do that?

Fortunately, the latest versions of Excel (2007 & 2010) allow for what’s called Conditional Formatting. This feature gives you the ability to change formatting of cells and ranges based on criteria you set. Let’s see how we can manage our agreements’ deadlines with this feature.

A scenario of three agreements having different Expiry dates, 101 (expires in less than 10 days), 102 (expires in nearly 3 months), and 103 (already expired). Notice that the current date is March 8th, 2012.

We need to use Conditional Formatting to highlight the expiry date in green background whenever the agreement is expired or expires in less than 10 days as of the current date.

From Home tab, Styles, Conditional Formatting select Manage Rules.

Hit the New Rule button.

From Select a Rule Type, select the “Use a formula to determine…” option. Then write the formula shown in the figure above. (D3 is the reference cell for the Expiry Date of the first agreement. Make sure you remove the dollar sign ($) from $D$3 using the F4 button).

Select the formatting into which the approached Expiry Date cell will turn into once the condition is met.

Hit OK buttons. The first agreement’s expiry date is highlighted in this example as it expires in less than 10 days.

You need to apply the formatting to other agreements by dragging the first cell then selecting the Fill Formatting Only option from the Paste Tip.

You’re done. Agreement 102 is not formatted as its Expiry Date is still far, whereas 103 is already expired and must be highlighted.

Test your work by changing 103’s expiry date to 25-Apr-2012, it turns un-formatted.

With this dashboard, your life is much easier now, I believe, in managing agreements’ renewal deadlines. Every time you open the workbook, Excel will give you the status of your agreements with warning lights on the ones approaching expiry.

From time to time, people approach me with an Excel problem that looks easy to calculate while, unfortunately, there’s no straightforward solution to it. The problem is calculating the difference (in hours) between two date-time formatted cells.


Cell A1: 2/15/2012  10:00:00 AM

Cell B1: 2/18/2012  08:00:00 PM

What is the elapsed time between both dates (in hours)?

You can get the answer by using the below formula in cell C1:

= INT(B1-A1)*24+(((B1-A1)-INT(B1-A1))/0.04166666)

The result will be: 82 HRS








Formula rationale:

Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59. So, an hour is about 0.041666 of the 0.99999.

Hence, with multiplying the integer part of the date serial number by 24 (to get number of hours for the whole elapsed days) and adding the result to the number of hours represented in the fraction part (the fraction part divided by 0.014666) you get the total number of elapsed hours between both dates.