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.
- From the menu select Spread | Calculate | Date Fill.
- 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).
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. |
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.
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. |