OpenOffice Calc Tips

Here is a useful formula - a long with a variant if you need to
enter dates quickly without the delimiters between the days, months and years.

In the example below, we enter our shorthand dates in column B. This column is formatted as Text so that any leading zeroes are not discarded by OOo Calc as it interprets the numbers entered as an integer.

Column C is formatted as a Date and the formula entered in C2 is

=DATEVALUE(TEXT(CONCATENATE(LEFT(B2;2);”/”;MID(B2;3;2);”/”;RIGHT(B2;2));”MM/DD/YY”))

Instead of using the CONCATENATE function, you can use the corresponding “&” operator.

=DATEVALUE(TEXT(LEFT(B3;2)&”/”&MID(B3;3;2)&”/”&RIGHT(B3;2);”MM/DD/YY”))

The WEEKDAY function returns an integer that represents the day of the week. The usefulness of this function is in identifying weekends in a series of dates.

It is straightforward to use the filldown functionality of Openoffice Calc to create a column of consecutive dates. We can use the WEEKDAY function to only include the days of the week we want - for example excluding weekends.

The arguments to the function are :

WEEKDAY(Number; Type)
where

• Number, as a date value, is a decimal for which the weekday is to be returned.
• Type determines the type of calculation. For Type =1, the weekdays are counted starting from Sunday (this is the default even when the Type parameter is missing). For Type =2, the weekdays are counted starting from Monday =1. For Type = 3, the weekdays are counted starting from Monday = 0.

In the example below, we use the WEEKDAY function to identify weekend days.

We now use the WEEKDAY function to create a column of dates that exclude the weekends. An alternative (but similar) approach is shown

here

The sheet for this example can be downloaded here

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.

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”)

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)&”/”&MID(B3;3;2)&”/”&RIGHT(B3;2))

Tomorrow, we will look at how to do this in a macro.

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

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

(more…)

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.

(more…)

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!

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…