1. Home
  2. Fill a Column with Dates or Times

Fill a Column with Dates or Times

Select menu: Spread | Calculate | Date Fill

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

  1. From the menu select Spread | Calculate | Date Fill.
  2. Fill in the fields as required then click OK.

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


The increment used to form intermediate values in the sequence. The type of increment is chosen from a dropdown 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 dropdown 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 restricted/filtered rows

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

Top Start filling from the first cell in the column.
Current Start 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

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


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.

Action buttons

OK Fill column with list and close the dialog.
Apply Fill column with list and leave dialog open.
Cancel Close the dialog without further changes.

See also

Updated on March 18, 2019

Was this article helpful?