Creating A Series Of Workdays4

To create a series of dates in a range, with just weekdays (Monday through Fridays), you first enter your starting date in a cell (B2, in the example below), and then enter the following formula in the cell below that cell.

=IF(OR(WEEKDAY(B2+1)=1;WEEKDAY(A4+1)=7);B2+3;B2+1)

Then use the Fill Down utility to fill out your entire series of dates.

The formula to display the day of the week we have already met…

=CHOOSE(WEEKDAY(B2);”Sun”;”Mon”;”Tue”;”Wed”;”Thu”;”Fri”;”Sat”)

Read More

Charting: Creating a Gantt chart

If you are familiar with project management and the various tools that are used to schedule deadlines and resources, you may be familiar with Gantt charts.

In this particular tutorial, we look at using stacked bar charts

First, we need to tabulate our project data. The numbers in columns D & E are total days completed & estimated days remaining in each task respectively.

Selecting the above table, we invoke the chart wizard with Insert – Chart.

Both the first row and column are selected as labels..

Select the Bar Chart type – below…

Of the different bar chart types, we choose the stacked variant – below…

Here, we add a meaningful title..

The ‘finished’ chart. We have some postprocessing further to perform before it looks like a Gantt chart. Select it for editing…


The blue bar above represents the start date of each task. We will make it disappear. Fist double click on any blue bar to invoke the Data Series editor below. Set the area fill to None and remove the border.

This is what the chart looks like with the blue bars removed …

Now select the Y axis for editing as shown below…

We now want to adjust the Scale settings for the Y-axis based on the timelines defined in the initial table.

The major interval (61.0) is approximately 2 months. The minor interval (1.0) is one day.

On the Label tab, we rotate the text to almost 270 degrees as shown below…

The finished Gantt chart…

Read More

Converting time to minutes past midnight

To convert a time to the number of minutes past midnight, just multiply by 1440 – as shown below.

You also need to make sure the result cell is formatted as a number.

Creating A Series Of Workdays

To create a series of dates in a range, with just weekdays (Monday through Fridays), you first enter your starting date in a cell (B2, in the example below), and then enter the following formula in the cell below that cell.

=IF(OR(WEEKDAY(B2+1)=1;WEEKDAY(A4+1)=7);B2+3;B2+1)

Then use the Fill Down utility to fill out your entire series of dates.

The formula to display the day of the week we have already met…

=CHOOSE(WEEKDAY(B2);”Sun”;”Mon”;”Tue”;”Wed”;”Thu”;”Fri”;”Sat”)

Easter formula

Scanning the Excel boards for a formula for Easter that is easily ported to Openoffice Calc.

=FLOOR(“5/” & DAY(MINUTE(B5 / 38) / 2 + 56) & “/” & B5; 7) – 34 + 1

where B5 is the year we wish to find the date on which Easter (Sunday) falls.

This generates American date format MM/DD/YYYY and I believe it is valid to 2075 – which is long enough for me!

Converting text to dates

You have lots of dates to enter in your spreadsheet. This can be tedious when you have to format it correctly. Here is a formula that allows to to quickly enter dates as text.

In the example below, we enter text in column B and convert to dates in column C. The conversion formula for C3 is
=DATEVALUE(LEFT(B3;2)&”/”∣(B3;3;2)&”/”&RIGHT;(B3;2))

Custom Time Formatting for a timesheet

In the timesheet example below, to represent the total hours worked for the week, we use a custom time format [H]:MM

Cells E3:E7 are also formatted similarly. If we had used HH:MM, E3 for example would display 07:15 – which is not quite what we are looking for.

Cells C3:D7 are formatted HH:MM AM/PM

Determining whether a year is a Leap Year

This tip explains how to determine whether the year in a date used in a OpenOffice Calc spreadsheet is a leap year.

In the Gregorian calendar, a normal year consists of 365 days. Because the actual length of a sidereal year (the time required for the Earth to revolve once about the Sun) is actually 365.25635 days, a “leap year” of 366 days is used once every four years to eliminate the error caused by three normal (but short) years. Any year that is evenly divisible by 4 is a leap year: for example, 1988, 1992, and 1996 are leap years.

However, there is still a small error that must be accounted for. To eliminate this error, the Gregorian calendar stipulates that a year that is evenly divisible by 100 (for example, 1900) is a leap year only if it is also evenly divisible by 400.

For this reason, the following years ARE NOT leap years 1700, 1800, 1900, 2100, 2200, 2300, 2500, 2600 because they are evenly divisible by 100 but NOT by 400.

The following years ARE leap years
1600, 2000, 2400
because they are evenly divisible by both 100 and 400.

The following formula will determine whether the year number entered into a cell (in this example, cell B3) is a leap year:

=IF(OR(MOD(B3;400)=0;AND(MOD(B3;4)=0;MOD(B3;100)<>0));”Leap Year”; “NOT a Leap Year”)

Date & Time : Calculating the end-of-month

A commonly needed entity is the date of the last day of a particular month or a range of months, usually when payments are due or when interest is calculated. We show two approaches to this.

The EOMONTH function returns the last day of the month based on the specified date and offset value. For the current month, the offset is 0 and the offset is incremented by 1 for each subsequent month – as is illustrated below.

An alternative approach is to use the DATE function. A little known feature of the DATE function is that a zero in the day argument results in the last day of the previous month being output. The example below shows how this could be implemented.

Date & Time : Calculating Dates of Holidays

This particular exercise in developing formulas for each of the major US holidays is a good way to develop expertise in OOo Calc date manipulation. US holidays follow simple guidelines for the dates on which they fall. Some holidays fall on specific dates , such as New year’s day on January 1st and independence day on July 4th. Other holidays fall on specific days within the month. For example, Memorial Day is the last Monday in May and Thanksgiving Day is the fourth Thursday in November.

The OOo Calc functions that will feature prominently in this exercise are

  • DATE(year; month; day) Returns formatted date corresponding to specified year, month, and day values.
  • WEEKDAY(dateValue; type) Returns a decimal value corresponding to the day of the week for the input date.

Before we tackle the US holidays problem, let us become more familar with the above functions and how they are used in OOo Calc formulas.

The DATE function allows us to define and manipulate a dates components – year, month, and day – independently. We have already seen this in an earlier tip

The WEEKDAY function returns a decimal value between 1 & 7 corresponding to the day of the week for the specified date. By invoking the TEXT with the appropriate formmating, we can convert the output of the WEEKDAY function to something more meaningful. In the example below, both formats are shown. The last two formulas show one approach to determining the first day-of-week after a specified date.

The table below generates the 10 major US holidays for a specified year – in cell C2

  • New Year’s Day
    =DATE(C2;1;1)
  • Martin Luther King Jr. DayThis is the third Monday in January.
    =DATE(C2;1;IF(2<WEEKDAY(DATE(C2;1;1));10-WEEKDAY(DATE(C2;1;1));3-WEEKDAY(DATE(C2;1;1)))+14)
  • President’s Day This is the third Monday in February.
    =DATE(C2;2;IF(2<WEEKDAY(DATE(C2;2;1));10-WEEKDAY(DATE(C2;2;1));3-WEEKDAY(DATE(C2;2;1)))+14)
  • Memorial Day The last Monday in May, we subract 7 days from the first Monday in June.
    =DATE(C2;6;IF(2<WEEKDAY(DATE(C2;6;1));10-WEEKDAY(DATE(C2;6;1));3-WEEKDAY(DATE(C2;6;1)))-7)

I leave the rest for you as an exercise!

Date & Time Basics

Creating a column of consecutive dates is easy with the OOo Calc tool.

The formulas below illustrate how to increment a particular date by a given number of days, months, or years…

Read More

Webquery : Importing HTML tables

With the help of the Web Page Query OpenOffice.org Calc import filter, you can insert tables from HTML documents in a OpenOffice.org Calc spreadsheet.

Let us step through the relatively straightforward process of importing data from tables in a HTML document.

The data for this example was obtained from an excellent website with tons of population data from all over the world : citypopulation.de

Here is what the source HTML file looks like : swiss.html

For this example, we will be importing awebpage from the local disk.

The External Data dialog is activated by Insert-External Data.

Once we fill in the path to the local copy of the HTML file and the table inside this file we wish to import – we hit OK and we are done – it is that easy.

Read More

Charting: Editing charts : part 1

In this series of tutorial, we will take a newly created chart and make use of the various chart editing tools provided by OOo Calc to adjust the appearance of the chart to our satisfaction.

In the example below, the chart has just been created from the data in B2:C11 However, we would like to make some changes – making the chart background transparent, adjusting the orientation of the x-axis labels, changing the format of the y-axis numbers and correcting the label on the right hand side.

The first task is the chart background. We would like to make it transparent. We first select the chart by double (left) clicking inside it’s borders. Then we invoke the Chart Area dialog by (right) clicking and selecting Chart Area as shown below.

With the Transparency tab selected, we make the desired change as shown below. Click OK

The end result is much more pleasing to the eye. But we are not finished yet with our chart modifications. Next, we will adjust the orientation of the x-axis labels.

Read More

Macros: A Lotto Number Generator

I have plans to implement a Keno game in the OOo basic language – but first a simple Lotto number generator. If you go into the source, you can configure to your own requirements. Generating a random number between 1 and 49 is really simple. Making sure that number has not already been picked slightly complicates matters.

Read More

Introduction to the Status Bar

How many of us have paid any attention to the row of small windows at the bottom of the OpenOffice Calc window? In this article, we will explore the Status Bar in more depth.



The Status Bar displays information about the current sheet. It is shown below in the default configuration with the different fields tagged.

The fields in the default configuration are:

  • 1 : Position The number of the current sheet and the total number of sheets in the document.
  • 2 : Page Style The Page Style of the currently open sheet. You can double-click in this field to call up the Page Style Editor.
  • 3 : Zoom The current page display zoom factor.
  • 4 : Insert Mode The current insert mode. This field is active when the cursor is in the input line of a formula or cell. By clicking in this field, you can toggle between INSRT = insert and OVER = overwrite.
  • 5 : Selection Mode The current selection mode. By clicking in this field, you can toggle between STD = Standard (default), EXT = Extend and ADD = Add.
  • 6 : Modified Flag If modifications to the current document have not been saved, a * is displayed here.
  • 7 : Formula A basic evaluation of a simple formula for the currently selected range is displayed here. Default is SUM

The Status Bar can be customized by clicking Tools – Configure and selecting the Status Bar tab. The Status Bar configurtion dialog window is shown below.

Read More

Character Codes

OOc calc uses the ANSI character set. By using the CHAR function, any member of the ANSI set can be generated. Conversely, the ANSI character code can be determined with the CODE function. The sheet below displays all the ANSI characters.

Read More