Wednesday, 28 September 2011

Current date and time : Excel keyboard shortcuts

(Note: "Ctrl + ;" should be read as "Ctrl" key and ";" key pressed together

Press Ctrl + ; (semi-colon) to input the current date in a cell. The input date will be the current system date and will not change.
Press Ctrl + Shift + ; (semi-colon) to input the current time in a cell. The input time will be the current system time and will not change.
(To input both Current date and time in a cell: - Ctrl + ; (then input a space using the keyboard space bar) followed by Ctrl +Shift +;

Depending on the requirements, you can also use functions as below:
Current date (dynamically changing):
In a cell input =Today() and press Enter.  This will input the current date in the cell. As the date is based on the function TODAY, unlike the keyboard shortcut above, this date will change when the system date changes, for instance, when you open the Excel file again on another day later. 
Current time (dynamically changing) :
In a cell input =Now() and press Enter. This will input the current time (along with the current date) in the cell. As the time is based on the function NOW, unlike the keyboard shortcut above, this time will change when the system time changes, for instance, a few minutes later. 

Hint: Use TODAY and NOW in Excel Sheets which are used for printing reports. They will automatically update and show the current date / time 

Thursday, 22 September 2011

Excel : Keyboard shortcut for AutoSum

Summing (Totaling)  is a frequent task in Excel.

In the picture below, we have a block of values in column H and wish to get the total in a cell  below the last value.

In a cell below the block , press Alt and = keys together followed by Enter.  This is called  AutoSum. This is also available as a Tool button :

Note: We can do this for a block of cells in a row too by using the above keyboard shortcut in a cell to the right of a row block.

How it works? Excel checks if there is a continuous group of cells with numbers above (column block) , it then checks to the left (row block) and displays the formula. Before pressing Enter, we can check the displayed formula and manually change it in case Excel has not guessed it right.

Tuesday, 20 September 2011

Dates in Excel - Getting them right !

In our daily work, we need to work with dates in Excel. Doing it the right way not only makes the task easy, but also ensures input of correct data. Once dates are correctly entered, we can find the number of days between two dates. This is necessary for interest calculation, fees calculation, etc. We can also calculate totals for a range of dates in case of sales data, group dates to get sales totals for a month and so on.

Suppose we input 05/12/10 in an Excel cell.  Excel may retain that format or it may change the display to something else. The behaviour is often puzzling. Has Excel understood the date as 5th December 2010 (as I intended) or has Excel interpreted it as 12th May 2010 which will certainly result in wrong calculations?  How do I enter dates as dd-mm-yy as we do in India? (while at the same time ensuring that my friend Bob in the US sees them as mm-dd-yy as they do there)

How do Excel dates work and how can we ensure better control over dates?

First step: Getting the dates settings right in Windows.
Windows Regional Settings (Windows XP):
Go to Start -->Control Panel --> Regional and Date Settings. In the dialog box (see below), select English (United Kingdom)  and click on Customize...:

On clicking Customize you will see the following dialog box:. (Ensure that you click on the Date tab at the top of the dialog box to make it the active tab):

Click on the down arrow to the right of the Short Date format and select dd-MM-yyyy format  
Click on the down arrow to the right of the Date Separator and select from  . (period/full stop) or - (hyphen) or / (slash) . You are indicating to windows that you will be using this as the date separator while inputting dates. Please select your preferred separator. 
Click on the down arrow to the right of the Long Date format and select dd MMMM yyyy 

Click on OK, OK and come out of the Control Panel. 

Once we modified settings as above and assuming  the date separator selected is "-" (hyphen), we can now  input "10-05-10" and be sure that Excel has understood it as 10th May 2010. 
(To verify this, select the cell with the date and press Ctrl + Shift + # 
The date will now be displayed (formatted) as 10-May-10 or 10/May/10 depending on the date separator setting.)  

How are two digit years interpreted?  If we input a two digit year from 00 to 29, Excel will prefix it with 20. Thus 10 becomes 2010, 15 becomes 2015, 05 becomes 2005 and so on. If we input a date from 30 to 99, Excel will prefix it with 19. Thus 56 becomes 1956, 31 becomes 1931, 99 becomes 1999 and so on. If we intend to input 2030, we will need to input it as a four digit year i.e. 2030. Similarly, if we intend to input 1910, we will need to input it as 1910 and not 10 because 10 will be converted to 2010. (This is because of the settings in the Date tab of Control Panel - see graphic above). 

Also, note that although we have input the date in the dd-mm-yy format as above, the person to whom we mail an Excel workbook will read the date in format as in his machine. So 10th May 2010 which is displayed as 10-05-2010 in my machine will be displayed as 05/10/2010 in his machine if his Regional Date settings are mm/dd/yyyy 

Important Note: Excel stores dates internally as numbers. This is the reason why a date is correctly displayed as either 10-05-10 or 05/10/10 in machines with different date settings. Display of dates can be controlled through formatting which will be discussed separately.

(The above settings in Control Panel need to be made only once in a PC. If you are not able to do this in an office environment, it could be because you are not logged in with administrator rights. In which case, you need to contact the System Adminsitrator for help). 

Monday, 19 September 2011

Weekday of a date in Excel

What day of the week was 15-Aug-1976? Or  26-Jan-2015?

In Excel:
In Cell A2 (or any other cell) input the date as 15-aug-76 or 15/aug/76
In cell B2,input the formula =TEXT(A2,"ddd"). The weekday will be displayed.

Note: If instead of "ddd" , "dddd" is used in the formula, then the date will be displayed as "Sunday". 

Friday, 16 September 2011

Quick Economics - Petrol price hike - how it works.

Assume that the price of one litre of crude oil (from which Petrol is made) is one US Dollar (USD). Also, assume that an USD (US Dollar) can be purchased from a Bank for Rs. 42.  This means the cost of a litre of crude oil is Rs. 42/-. After adding other input costs, processing costs, overheads, taxes, profit margins, etc., the selling price of Petrol would be  determined.

Scenario 1: The price of crude oil remains stable but the rupee weakens: Assume that over a period of time the rupee weakens (i.e. you need to pay more rupees to buy one unit of a foreign currency, in this case the USD) and the price of a USD has risen from Rs. 42 to Rs. 56.  This means that for a litre of crude oil you need to pay Rs. 56 now as against Rs. 42 earlier. Other things remaining the same, the selling price of Petrol is  bound to go up.

Scenario 2: The price of crude oil goes up but the rupee remains stable:.  Assume that the seller abroad has hiked the price of crude oil to USD 1.33 per litre.  Now you have to pay the rupee equivalent of USD 1.33 i.e.  Rs. 56 to buy a litre of crude oil. Other things remaining the same, the selling price of Petrol is  bound to go up.

We can conclude that the price of imported goods/services are bound to go up when the price of the foreign currency goes up or there is a hike in the  price of the foreign goods/services.

A weak rupee and impact on exporters. The above scenario covered importers, people who pay in Foreign exchange. What about exporters (IT Companies/BPOs, manufacturersm etc), people who receive money in Foreign currency for goods / services sold ? In the above example, these people will benefit from a weak rupee as they will be able to get Rs. 56 now as against Rs. 40 earlier for each USD that they will convert into Rupees.

(The above is a simplistic basic explanation. The prices are also imaginary and are for illustrative purposes only. )