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:
=TODAY()
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:
=NOW()
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+;
Want to Improve Your Excel Skills?
Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.
To Get Most out of Excel, Learn the Pivot Table techniques in our
Pivot Table Masterclass Training!
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.
=NETWORKDAYS(A1, A2)
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.
= MONTH(A1)
5. Getting only the Year of a Date
If you wish to extract the YYYY portion of a date, simply say:
=YEAR(A1)
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… 🙂
Cheers,
Are you facing any problem in using Excel? Any Question?
You have come to the right place. Tell us your needs. We’ll be glad to help you!
I’m trying to calculate the difference between two days resulting in actual days (including weekends). Is this possible?
Hi Teresa,
Yes, this is possible. If the two columns are dates, simply subtract them.
For example, if A1 contains 1/1/2014 and b1 contains 1/4/2014, then simply write a formula B1 – A1. You will get a 3. If you were expecting a 4, then the formula can be modified to B1 – A1 + 1.
Hope this helps – Vinai