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)&”/”&MID;(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!