1. Home
  2. Fill with Dates or Times

Fill with Dates or Times

Date fill lets you 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. Select Spread | Calculate | Date Fill.
  2. From the dropdown list select the column you want to fill.

     

  3. Enter values as required then click Apply to fill the column and keep the dialog open.
  4. To fill other columns repeat from step 2 then click OK when all required columns are filled.
Options

Date format Specify the format used to display dates.
Base date Specify 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 Specify the first value in the sequence.
Ending value Specify the last value 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.

You can generate a decreasing series or sequence by specifying a negative multiplier along with a Starting value that is greater than the Ending value.

Number of repeats Lets you specify a pre-multiplier so that each value is repeated a specific number of times.
Ignore restricted/filtered rows 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. For example, if cells 2 and 5 are restricted you will get the sequence 1 * 2 3 * 4.
Fill selected rows only

This option is only available when there is a row selection active in the spreadsheet. The fill operation will then just apply to the selected rows, i.e. skipping the unselected rows.

The numerical sequence applied to the selected cells will be 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 values 1,2,3,4 respectively and rows 2,4,6,8 would have their values 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  – Fills from the first cell in the column.
  • Current – Fills from the cell specified in Current cell to fill from. By default this is the cell that has the cursor focus.
Fill to
  • Bottom – Fills all the cells, recycling the list if needed.
  • End of list – Fills the cells until the list runs out, then stops.
Current cell to fill from Specify the starting cell for the sequence.

Updated on December 14, 2018

Was this article helpful?