Excel How to find difference between two dates (in years/months/days)

How many ( complete ) years are there between 15-Oct-1983 to 19-Jan-2010?

In Excel:

In cell A2 input 15-Oct-1983 or 15/Oct/1983

In cell B2 input 19-Jan-2010 or 19/Jan/2010

In cell C2 type the formula =DATEDIF(A2,B2,"y")

Notes: 

The "y" in the formula can be in upper or lower case. It SHOULD be in DOUBLE quotes. 

The function DATEDIF can be entered in lower case of upper case but should be spelt correctly. 

Hint for months and days:

To get difference in completed months, replace "y" with "m" in the formula

To get difference in completed days, replace "y" with "d" in the formula

Note 1: If we use DATEDIF to calculate the number of days between 15-Mar-11 and 18-Mar-11 using "d" as the third argument in the formula, the answer will be 3 (i.e. it will exclude the end day). If we intend to include the end day in the calculation, then we should use the formula =DATEDIF(A2,B2,"d") + 1
 

Note 2: From Excel 2007 onward, the DATEDIF function is built into Excel. For Excel 2003 and earlier, go to Tools-->Add Ins--> Analysis Tool Pack --> OK to enable the function. 

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 

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.

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). 

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". 

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. )

Gmail : Quick way to find Mail/Chat conversations

Here's a quick way to find all mail/chat conversations with a particular contact in Gmail.
Let's say, I want to find all  mails exchanged with Bhau Ghadge. I proceed as follows:

Step1: In the left pane in Gmail, look for the box for searching contacts:

Step 2: In the box, start typing the contact name. Once the name is displayed, move the mouse to the name and then to the right, locate the menu "View Recent Conversations" and click.

Recent mails/conversations will be listed in the right pane.  

How SBI's Green Channel Banking works..

State Bank of India (SBI) launched Green Channel Banking (paperless Banking) at select branches some time back. SBI's Personal Banking Branch at Goa-ves Belgaum is now providing the service to its customers.

Here's how it works:

A POS (Point of Sale) machine (similar to the one seen at Shopping Malls) has been placed at desks. The customer swipes his Debit Card and is offered three choices: Cash Withdrawal, Cash Deposit and Funds Transfer.

Using the Cash Withdrawal option, he can withdraw more Cash than he can at the ATM. Also, unlike the ATM, here he can choose the denomination of notes.Similarly, he can deposit Cash using the Cash Deposit option.

I tried out the Funds Transfer option. On selecting the option, I was asked to input the beneficiary's Account number. The system showed me the beneficiary's name and asked me to input the amount to be remitted. I was also asked to authenticate the transaction by inputting my Debit Card PIN(Personal Identification Number). On successful authentication, a message popped-up on the Bank official's screen. On his confirming the transaction, the transaction was completed and a receipt issued showing all details of the transaction.

There are no charges for use of the facility.

About Me

I am currently ( 2017) working as a Sr. Principal Consultant at Atyati Technologies Pvt. Ltd (formerly a Genpact Company)  a company engaged in providing solutions for Financial Inclusion and Micro Finance. My work description includes Business Analysis, Compliance, Quality Assurance, Reconciliation, Automation, Documentation, Anomalous transactions detection by Data analysis etc

I worked as Banking and Finance Domain Consultant at Oracle Financial Services Software Ltd. Mumbai (Formerly, iflex Solutions) in the Software Quality Assurance Department.My work areas included: Functional Specifications Reviews, Test Plan Reviews,  Error Analysis, Documentation Quality Audit, Risk Management, Bid Profitability reviews, etc. I conducted several Advanced Excel Training Programs in the Company. 

Prior to that, I was Chief Manager (IT-Internet Banking) at State Bank of India (SBI)’s Global IT Centre at  Mumbai. i was in-charge of Technical Support, Security, Vendor Management, Contracts, Training aspects of Internet Banking in State Bank of India and its 7 associate Banks. I also won the “Best Trainer” award in SBI at all India Level in 2002 for my  contribution to popularizing use of IT in the Bank during his stint as Faculty Member at the SBI’s Staff Training Centre at Bangalore. I conducted MS-Excel related trainings of different levels during his tenure at SBI.  I worked with SBI from 1980 to 2007 earning rich experience in various capacities in varied Banking Domains: Foreign Exchange, Branch Administration, Retail Lending, Commercial Lending, Agricultural Lending, SME Lending, Personnel (HR),Training, IT (Internet Banking)

Advanced Excel Training/Consultancy at:

Oracle Financial Services Software, Pvt. Ltd. Goregaon, Mumbai

State Bank of India, Staff Training Centre, Bangalore

State Bank of India, Internet Banking Department, Belapur, Navi Mumbai

Institute of Management Education and Research (IMER), Belgaum.

Reynolds Chemequip Pvt. Ltd.  Belgaum

N.G.E.F (Hubli) Ltd. ,Hubli

HLL Healthcare Ltd, Kanagala Belgaum

Sterling Tapes Pvt. Ltd. Belgaum

BTP Structural (India) Pvt. Ltd. Belgaum

Vayavya Labs. Pvt. Ltd., Bangalore/Belgaum

Noorani Corbel Developers Pvt. Ltd. ,Belgaum

Malwan Beach Project, G.S.S. College, Belgaum

Jain Yuvak Mandal’s Educational Institutions, Belgaum

Belgaum Cancer Hospital, Belgaum

Sahyadri School (Krishnamurti Foundation India), Tiwai Hills, Near Rajgurunagar, Pune

Weekend Excel Course for office-goers and professionals a IMER, Belgaum

Other Assignments:

Visiting Faculty at Institute of Management Education and Research, Belgaum

Also I lecture on Finance, Banking at various colleges and institutions.

Contact details:  Rajendra Pai : Mobile: 94835 19900 Email: rspai9@gmail.com

My previous website with Useful Links