An Introduction to working with dates in Excel

Glen Feechan
CEO
Columnist

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.

Replies

07th Oct 2010 13:13

Weekly Dates in Excel

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.

Thanks (0)
By KH
07th Oct 2010 14:34

weekly updating

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

Thanks (0)
07th Oct 2010 18:09

Weekly Dates in Excel