Save content
Have you found this content useful? Use the button above to save it to your profile.

An Introduction to working with dates in Excel

5th Oct 2010
Save content
Have you found this content useful? Use the button above to save it to your profile.

 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.

Tags:

You might also be interested in

Replies (4)

Please login or register to join the discussion.

avatar
By Adrian Everton
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)
avatar
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)
avatar
By theaaman
07th Oct 2010 18:09

Weekly Dates in Excel

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

Thanks (0)
Glen Feechan
By Glen Feechan
07th Oct 2010 19:44

Yet another alternative

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

[email protected]

Not Just Numbers Ezine

Free Excel Pivot Table Video

Thanks (0)