OO.o Calc: Skipping Days

Annika Backstrom
in misc, on 26 April 2007. It is tagged #Computers, #howto, #ooocalc, and #openofficeorg.

I'm a bit of a spreadsheet junkie. I had an occasion today to autofill a range of dates, but I wanted to exclude Mondays from the list. Here's how I did it.

Dates in OpenOffice.org Calc are measured by counting the days since Dec. 30, 1899. For our purposes we will start with a specific date in cell A1, ie. today:

=DATE(2007; 4; 26)

This cell and the ones that follow can be toggled between date and number formatting if you want to see the math behind the scenes. The next row in the column gets a formula:


This formula causes every sixth cell to skip a date because of the fraction math. In normal year/month/day formatting the decimal value is hidden, only apparent when a day is skipped. Take advantage of the built-in autofill feature and drag this cell down the sheet to create your range of dates:


At this point we start on a Thursday but skip each future Thursday. We can modify our A1 value to shift around the skip date:

=DATE(2007; 4; 26)+3/6

By shifting the date value in the first cell we can make the skip come three days earlier, dropping Mondays from the list.

Thu, April 26, 2007
Fri, April 27, 2007
Sat, April 28, 2007
Sun, April 29, 2007
Tue, May 1, 2007
Wed, May 2, 2007
Thu, May 3, 2007
Fri, May 4, 2007
Sat, May 5, 2007
Sun, May 6, 2007
Tue, May 8, 2007
Wed, May 9, 2007
Thu, May 10, 2007

If only that were so easy in real life.