An Introduction to working with dates in Excel

 I find that many clients are quite comfortable working with financial figures in Excel, but don't know how useful Excel can be when manipulating dates.

I thought that it might be useful to provide a short post on some of the most useful functions for handling dates in Excel.

One of the simplest functions is the TODAYfunction, which can be used on its own or within another formula to return today's date. It has no arguments and is essentially a variable (Excel still requires empty brackets after it so that it has the same format as other functions)..

Example:

=TODAY()          will display today's date in the cell


I covered YEAR, MONTH and DAY in an earlier post. These allow you to return the year, month or day respectively (as a number).

Examples:

Where cell A1 contains the date 5th October 2010,

=YEAR(A1)   returns 2010
=MONTH(A1)   returns 10
=DAY(A1)    returns 5

WEEKDAY allows you to identify the day of the week (as a number) of any date. It has two arguments, the first being the date you wish to use and the second (optional) argument being the number 1,2 or 3 depending on how you want to number your days.

1:  Sunday=1 through to Saturday=7
2:  Monday=1 through to Sunday=7
3:  Monday=0 through to Sunday=6

If this argument is not entered then it defaults to setting 1.

Examples:

Where cell A1 contains 5th October 2010 again (a Tuesday),

=WEEKDAY(A1)   returns 3
=WEEKDAY(A1,1)   returns 3
=WEEKDAY(A1,2)   returns 2
=WEEKDAY(A1,3)   returns 1

I hope this has made you feel a little more comfortable when working with dates in Excel.

Don't forget if you want to take the even easier route, we can build your spreadsheet for you at Spreadsheets by Email.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report.

Comments

Weekly Dates in Excel

Adrian Everton | | Permalink

 I am trying to input dates in a column that run weekly. How is this done. I tried "dragging" the previous date but this only brings up the next day.

KH's picture

weekly updating

KH | | Permalink

-- KH

if your starting cell is A1, and that has your start date, say, 3 jan 2010, then in cell A2 type =(A1+7) and copy that cell down your column. Then every following cell should show the previous cell date plus one week.

Weekly Dates in Excel

theaaman | | Permalink

Adrian

If you do not want to use formula's you can highlight the number of columns you want to fill with the dates and choose (in Excel 2003) Edit -> Fill -> Series.  Leave all the default values apart from the step value - you will need to change this to 7.  Click 'okay' and the new dates will be populated.

HTH

Antony

gfeechan's picture

Yet another alternative

gfeechan | | Permalink

All of these will work. alternatively do what you were doing but enter the first two dates (rather than just one), highlight them both and drag. Excel should pick up the sequence.

-- Glen John Feechan BA Hon. ACA

glen@feechan.co.uk

Not Just Numbers Ezine

Free Excel Pivot Table Video

Add comment
Log in or register to post comments
This blog

Popular posts from Glen Feechan's Not Just Numbers blog - The blog for those who know it's not just about the numbers. Typical content included is primarily Excel tips and other comment relevant to those responsible for finance in their business. Glen develops spreadsheets for clients all over the world via needaspreadsheet.com and helps accountancy practices to make better use of Excel through his Excellent Accountancy business.

Get The 5 Excel features that you need to know free, by subscribing to Not Just Numbers here.