There are many in-built Excel Functions that can help you in working with Dates and Time. It can assist you in showing today’s date, the current time, and represent dates as Months, Years, and even calculate difference in 2 dates.
1. Getting Today’s Date
In the cell where you want to display today’s date, key in the following formula:
Depending on your PC’s Regional Settings (Setable in Control Panel), you will get today’s date displayed in MM/DD/YYYY or DD/MM/YYYY format.
The problem will this approach is that every the Excel file is re-calculated, the date will change and display the latest day’s date.
If you wish to only display today’s date, but do not want it to change in the future, there’s a little known shortcut to do this. Go to the cell where you want to get today’s date, and simply press Ctrl + ;
2. Getting Today’s Date & Time
In you want the date and time, both to be displayed, more like a time stamp, use the following formula:
It will display as 24/7/2012 12:33
If you only want the time, like a time stamp, and do not want the time to ever change, then simply press the following keys: Ctrl + Shift + ;
And if you want to display both the date and time stamp in a single cell, combine the 2 functions
Select a cell and press CTRL+; then press the SPACE BAR to add a space, and then CTRL+SHIFT+;
3. Get the Number of Days in between 2 Dates in Excel
To get the difference in 2 dates, you can simply subtract them, like:
=B6 – A6
If you only want the Weekdays, excluding Sundays, you can use a hidden Excel gem – Networkdays function.
It will calculate the difference between A1 and A2, without counting the Sunday.
4. Get the Month of a Date
To get the Month, displayed as a number – for example: July will be 7.
=MONTH( TODAY() ) will give you a 7 in July.
5. Getting only the Year of a Date
If you wish to extract the YYYY portion of a date, simply say:
For 31-July of the Year 2012, it will yield 2012.
As you can see, Excel’s in-built Date and Time Functions are really useful, and handy. Do master them, and you will save a lot of time.
Let me know how’s your experience in handling dates in Excel. You can post a comment below… 🙂