BInterest

How many times, you need to enter or are willing to enter current time & date in the excel sheet but could not do so. The Answer is many times., One of the reader of this blog, was in need of the ways to enter current Date & Time into the Excel Sheet, he was working on so that he could track the time taken to complete it. Entering Current Date & Time (Also know as TIME Function, could be achieved via two methods, one by shortcut / Hot-keys & secondly by Formula, we here in this post would look at both the type of methods : Before providing you with the methods, a better & easier way to use DATE & TIME Function would be right from the Ribbon itself (See the Image below)

Keyboard Shortcuts to Enter Time & Date :

1. To Enter the Current Time in the Selected Cell, Press Ctrl + Shift + ; Key
2. To Enter the Current Date  in the Selected Cell, Press Ctrl + ; Key

Time Formula’s to Enter Time & Date :

1. To enter the date with a formula, type:  =TODAY()
2. To enter the date and time with a formula, type: =NOW()

Note : Every time the worksheet is opened the cell containing the Date & Time using above methods would update automatically in accordances to the System Timing.

Calculate the Age via Time Function :

To Calculate the my age, Enter Birth Date & Time (19-11-1981  05:30:00) in B1 Cell, use =DATEIF Function of Excel. DATEIF Function calculates the difference between two dates in a  number of years, months, or days between the dates. So for calculating the Age, two dates available with us is the Birth Date & Current Date.

Formula for Calculating Age =DATEDIF(A1,NOW(),”y”)&” Y, “& DATEDIF(A1,NOW(),”ym”)&” M, ” & DATEDIF(A1,NOW(),”md”) & ” D” which results in 28 Y, 0 M, 12 D.

Calculate the Number of Days in a Month :

Suppose, you have the Date : 19-11-1981 given to you in Cell B1, and are interested in knowing, How many days where there in that respective month, type =DAY(DATE(YEAR(B1),MONTH(B1)+1,0)), which results in 30 D.

Calculate Which Week it is in a Year :

Enter =TRUNC(((B1-DATE(YEAR(B1),1,1))/7))+1+IF(WEEKDAY(DATE(YEAR(B1),1,1))>WEEKDAY(B1),1,0)

and many more., you can calculate many more days & dates using combination of TIME Functions like TRUNC, NOW(), TODAY(). View More Tips & Tricks from Excel 2007 & Excel 2010. Still using Office 2007, Get your free copy of Office 2010 Public Beta from Microsoft. Next time you are in need of the Current Time & Date in your Excel Sheets, don’t type it – Just Enter the Correct Formula and you are Done. Hope, this solves problem of many, who were afraid of using TIME Functions in the Excel.

Please Comments your view on the above solution to TIME Functions, and if you use any other type of method to enter TIME & DATE, please share with others via Comments. Comments are appreciated and motivates me to write more & more.,

You can follow me on Twitter at http://twitter.com/binterest.Do stay tuned to Binterest.com for more, Best Way 2 do it, Subscribe to Binterest's Feed Updates