OO.o Calc: Skipping Days
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:
=A1+7/6 =A2+7/6 =A3+7/6 =A4+7/6 =A5+7/6 =A6+7/6 =A7+7/6 =A8+7/6 =A9+7/6 =A10+7/6 =A11+7/6 =A12+7/6
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.