Generating a series of dates:

First, here’s a brief explanation of how Excel calculates dates and times.
Excel treats the date / time values internally as a numeric value. The default is 1 for 1st January 1900, the number 2 for 2nd January 1900 etc.
If the numeric formatting is changed from “Number” to a “Date” or “Time”, for example, the value will be converted to 01/01/2011 12:00:00 40544.5.
The value associated with the preceding point corresponds to the day and, the value after the decimal point corresponds to the time.

Generating a series of dates using a formula:

Generating consecutive days.

Select cell D6 and select the long date format for this cell.(See Course Excel 2007 Lesson 16: Formatting numbers using the “Ribbon”).
In cell B6, enter a date, for eg: 14/01/2011 and press “Enter”.
Enter the formula = B6 +1 in cell B7 and press “Enter”.
Select cell B7 and copy it down with the fill handle.

To generate a set of dates separated by a week:

Select cell D6 and select the long date format for this cell.
In cell D6, type a date, for eg: 14/01/2011 and press “Enter”.
Enter the formula = D6 +7 in cell D7 and press “Enter”.
Select cell D7 and copy it down with the fill handle.

To generate a set of dates separated by a month:

Select cell D6 and select the long date format for this cell.
Enter a date in cell F6, for eg: 14/1/2011 and press “Enter”.
To determine what is to come in cell F7, we can use the function = DATE.
The DATE function has 3 parameters, i.e, year month day.
We can now enter the following formula in cell F7. Enter = DATE (2011, 2, 14) and then you will get the result with the long date format in the cell, i.e, Monday, February 14, 2011.
However we consider the YEAR, MONTH and DAY functions used in conjunction with the DATE function to determine what is to be entered in cell F7.
= YEAR: Returns the year of a date.
= MONTH: Returns the month of a date.
= DAY: Returns the day of a date.

Enter the following formula in cell F7, = DATE(YEAR (F6), MONTH (F6) +1, DAY (F6)) and press “Enter”.

The second closing parenthesis behind the formula is part of the opening parenthesis of = DATE.

In this example, the formula, including the year, the month and the day are taken from cell F6 and the month is increased by 1.
Select cell F7 and copy it down with the fill handle.

To generate a set of dates separated by a year.

Select cell H6 and select the long date format for this cell.
Enter a date in cell H6, 14/1/2011 for eg., and press Enter.
In cell H7, enter the formula = DATE (YEAR (H6) +1, MONTH (H6), DAY (H6)) and press Enter.
In this example, the formula, the year, the month and the day are taken from cell H6 and the year is increased by 1.
Select the cell, H7, and copy it down with the fill handle.

Generating a series of days:

(No Saturdays and Sundays):

Select cell J6 and select the long date format for this cell.
Enter a date (it must be a working day) in cell J6, 14/1/2011 for eg., and press “Enter”.
Select the long date format for cell J7.
Enter the formula, = IF (WEEKDAY (J6) = 6; J6 +3; J6 +1), in cell J7 and, press “Enter”.
Select the cell, F7, and copy it down with the fill handle.

The function = WEEKDAY returns the day of the week for a particular date.
The date is displayed, by default, as an integer from 1 to 7. 1 = Sunday 2 = Monday 3 = Tuesday 4 = Wednesday 5 = Thursday 6 = Friday and 7 = Saturday).

The formula works as follows:
IF (WEEKDAY of the cell J6 = 6, i.e, “Friday”, then 3 is added to the count, i.e, J6+3, which is “Monday “. If this is not the case then, the count is J6 +1).

Generating a series of dates by right-clicking the fill handle:

Generating consecutive days:

Select cell B6 and select the long date format for this cell.
In cell B6, type a date eg 14/01/2011 and press “Enter”.
Select cell B6 again, move the mouse pointer to the lower right corner of cell B6 and this will change to a four-headed arrow(1).
Right-click on the four-headed arrow and drag the mouse button down with the fill handle, and then release it. Then a menu pops up (2).
In this menu, choose the appropriate implementation.
In this example, we choose to implement Days (3).
The week option is not available in the menu.

The advantage of using a formula to generate date ranges is relative. When you want to adjust the dates of the entire series, only the first date should be changed.

You can download a sample file here.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats