Fill a Column with Dates or Times
See Also
This dialog can be used to fill a column with a series of dates or times. The sequence or series that is used to fill the column is inserted from row 1 onwards, and is repeated as necessary to fill the column. If the length of the column is not an exact multiple of the pattern length, the remainder of the cells will be filled with part of the pattern.

Date Format

This sets the date format that will be used to display the column once the dates have been entered into it.

Base Date

The base date that all other dates are numbered from.
1/3/1600 The nominal starting day for the Gregorian calendar.
1/1/1900 The starting day used by spreadsheets such as Excel, Lotus and Quattro.

Starting Value

First date or time in the sequence.

Ending Value

Last date or time in the sequence. If this differs from the Starting Value by an exact number of Increments it will be included in the pattern, otherwise the pattern will terminate at the value less than the ending value (greater than in the case of a negative increment).

Increment

The increment used to form intermediate values in the sequence. The type of increment is chosen from a drop down list of the following types:
Days - n day intervals
Weeks - n week (7 day) intervals
Months - n month intervals
Years - n year intervals
Hours - n hour intervals
Minutes - n minute intervals
Seconds - n second intervals
Daily - 1 day intervals
Weekly - 1 week (7 day) intervals
Monthly - 1 month intervals
Yearly - 1 month intervals
Hourly - 1 hour intervals
Twice daily - 12 hour intervals
Fortnightly - 2 week (14 day) intervals
Non-weekend days - 1 day intervals, skipping Saturday and Sunday
Pentade - 5 day intervals within each month, with the last interval reduced to 3 or 4 or extended to 6 days so that there are 6 periods per month
Decade - 10 day intervals within each month, with the last interval reduced to 8 or 9 or extended to 11 days so that there are 3 periods per month

The number of intervals for the chosen incremental type between each item in the series is specified in space provided alongside the drop list of types. Note that increments (daily, weekly etc) are a single unit and do not allow a multiplier to be specified.

A decreasing series or sequence can be generated by specifying a negative multiplier along with a Starting Value that is greater than the Ending Value.

Number of Repeats

Allows a pre-multiplier to be applied to the sequence, so that each element is repeated the specified number of times.

Ignore restrictions/filters

When selected, rows that have been restricted/filtered will be excluded from the cells filled. The sequence of values will be inserted into the cells, jumping over the filtered/restricted cells, so if units 2 and 5 are restricted you will get the sequence 1 * 2 3 * 4.

Fill Selected Rows only

This option is only available when a row selection has been made in the spreadsheet. When selected, the fill operation will only apply to the currently selected rows. The numerical sequence applied to the selected cells is contiguous. For example, if the fill sequence was 1,2,3,4 and rows 1,3,5,7 where selected, then rows 1,3,5,7 would be given the valued 1,2,3,4 respectively and the values in rows 2,4,6,8 would remain unchanged.

Fill all columns in selection

This option is only available when there is a column selection active in the spreadsheet, or row selection and the Fill Selected Rows only option has been selected. The fill operation will then be applied to all columns containing selected cells. This allows multiple columns to be filled in one operation.

Fill from Cell

TopStart filling from the first cell in the column.
CurrentStart filling from the cell specified in the Current cell to fill from option. By default this value is specified as the current cell. When selected, the option Current cell to fill from is enabled and the starting cell for the column to be filled can be specified in the space provided.

Fill to

BottomFill all the cells, recycling the list if needed.
End of listFill the cells until the list is exhausted and then stop.

Preview

This list illustrates the sequence of numbers that will be inserted in to the column. For very long sequences, the list only displays the first 20 and last 20 items, separated by a '...' item.

OK

Use the specified sequence of dates or times to fill the column and close the dialog.

Apply

Use the specified sequence of dates or times to fill the column and leave the dialog open..

Cancel

Close the dialog without making any changes.

See Also