Date and time in spreadsheet problem generator

Posted by Greten on 10 Apr 2013 under Free Instructional Applications

In certain sciences such as Physics and Economics, you may encounter problems that have time as one of its variable. You already encountered or will encounter something like:

The distance between Manila and Baguio is approximately 250 km. If a bus travels for 8 hours from Manila to Baguio, what is the average velocity of the bus?

Of course, given the distance and time as physical quantities, you can easily encode it to your spreadsheet problem generator in case you want to make similar problems in the future.

What if we modify the problem a little. Suppose we write it this way:

The distance between Manila and Baguio is approximately 250 km. If the bus left the bus station in Manila at 11:00 am and arrived in Baguio at 7:00 pm, what is the average velocity of the bus?

While many teachers and students can easily determine how to solve this problem, the format the time where given is not something we can readily encode in a spreadsheet problem generator. Note that the variables 11:00 am and 7:00 pm are pointers of specific time of the day in a clock, and cannot be readily subjected to mathematical computations.

How spreadsheet records the date and time?

Date and time includes several non-numerical variables such as months and AM/PM so it can be difficult to treat it as a number. However, LibreOffice/OpenOffice Calc (as well as MS Office) stores date and time not as how you see it, but as a special kind of number known as date serial.

A date serial is a number that counts how many days have passed since a predefined specific date. This predefined specific date differs between Microsoft Excel and Open Office Calc. For MS Excel, it's 1 January 1900 which it counts as 1. Thus, for 2 January 1900 it's 2, for 3 January 1900 it's 3 and so on. For OO Calc, it's 30 December 1899 which it counts as 0. Thus, for 1 January 1900 it's 2, for 2 January 1900 it's 3 and so on. OO Calc has the option to change the day 0 to 1 January 1990 or 1 January 1904 but I will no longer discuss it in this post since it will not have any effect in our spreadsheet problem generator.

The date serial allows us to treat date and time as single number that can be subjected to mathematical computations. The count of date serial does not stop when the month or the year changes. Thus, if we are using Open Office and is configured to have 30 December 1899 as day 0, then 31 December 1899 is 1, 1 January 1900 is 2, for 1 February 1900 is 33 and 1 January 2012 is 40,909. The time in date serial is counted as decimal part. For example, 1 January 2012 at 12:00 pm in date serial is 40,909.5.

Using the functions DATEVALUE and TIMEVALUE

For us to be able to construct spreadsheet problem generator that involves specific date and/or time as oppose to a measurement of time, we must use the DATEVALUE and/or the TIMEVALUE functions. The DATEVALUE function converts dates into a whole number date serial, while TIMEVALUE function converts a clock-given time to a decimal number date serial between zero and one.

You will use TIMEVALUE for problems that are implied to occur within a day and the DATEVALUE for problems or situations that take place within several days. You may use both of them if the problem involving several days also needs a precise answer that accounts for the time within those days.

Before we can use the DATEVALUE and TIMEVALUE functions in a spreadsheet problem generator, we need to see first how they work. Open your OpenOffice Calc or LibreOffice Calc (this also works in MS Excel) and enter a date and a time in two different cells.

You can type the date as 4-10-13, 04/10/2013, 10 April 2013 or April 10, 2013; the spreadsheet will recognize it as a date and reformat it to its own default formatting: 04/10/13. You can reformat it into another date format by right-clicking the cell containing the date and selecting "Format Cells", but its format in the input line is always 04/10/2013. You can only edit it from this format, but you can just erase everything and type a date in the format that you want (and the Spreadsheet will recognize it as date and reformat it according to the formatting of the cell).

calc-date-options

Time also has similar case. It may be encoded as 2:00pm (no space), 2:00 PM, or 14:00 and the spreadsheet will recognize it as a time data and reformat it in its default format. In Calc, it's 02:00:00 PM, has zero in front if the hour is a single digit, has two extra zeroes for seconds, and the AM/PM are in capital letters. Similar to date, you can make it appear in different format by right-clicking on the cell and selecting the "Format Cells" option, but the input line will always display it as 02:00:00 PM.

Now, to use the function DATEVALUE, move the cursor to the cell other than where you encoded your date and time. Then, encode the following:

=DATEVALUE(TEXT(B3, "mm-dd-yyyy"))

Just replace B3 with the actual cell that contains the date you encoded. For the function TIMEVALUE, encode the following in a cell other than where you encoded the date, the time, and the DATEVALUE function.

=TIMEVALUE(TEXT(D3, "hh:mm"))

You should get a whole number for DATEVALUE, and a decimal number less than 1 for TIMEVALUE. If we add the results of these two functions, we will get the date serial for that specific date and time.

There are other ways to configure the DATEVALUE and the TIMEVALUE function. Of course, you can have the date and time directly nested inside the functions instead of being called from other cells, but it is by calling it from other cells that we can make them useful in constructing our spreadsheet problem generator.

Here are downloadable examples that you can use as references. Note that these examples demonstrate only how to use the DATEVALUE and TIMEVALUE functions and we are not yet constructing our problem generator: DATE/TIMEVALUE in Open Document Spreadsheet (Libre/OpenOffice).

Constructing word problems that involves specific times

Using the Baguio-Manila example provided earlier. We can construct the problem generator first by encoding the given values (and if you want, mark them with border and labels to indicate that they are the given values). You should be able to get something like this.

timevalue-spreadsheet-problem-given

Next, pick one of the blank cells and encode the following:

=(TIMEVALUE(TEXT(F4, "hh:mm")) - TIMEVALUE(TEXT(D4, "hh:mm")))*24

Here, the F4 is the reference cell of the final time and D4 is the reference cell of the initial time. In our example, the final time is 7:00 PM while the initial time is 11:00 AM. Simply replace the F4 and D4 with whatever cells you use to contain the time variables.

What we did here is to get the TIMEVALUE of each time, and then subtract the date serial of the latter time with the earlier time. To get the actual number of hours, we multiply the difference by 24 since a serial number of 1 is equivalent to 1 day. From here, we should get that the time between 11:00 AM and 7:00 PM is 8 hours.

Then, pick another cell where you can encode the final answer. Since this is a simple speed problem, all we need to do is to divide the given distance by the number of hours we obtained.

=B3/H3

You should get something like this.

timevalue-spreadsheet-problem-solution

You may also remove the part wherein you compute the time separately and have only one cell compute the final answer.

=B3/((TIMEVALUE(TEXT(F4, "hh:mm")) - TIMEVALUE(TEXT(D4, "hh:mm")))*24)

Lastly, merge some of the cells and use the & sign to concatenate text with data obtained from the cells to generate the actualy word problem. You cannot format the concatenated cell values by right-clicking so use the function TEXT instead.

TEXT(F4,"h:mm AM/PM")

This format displays the time in hour:minute format with AM or PM. Without the AM/PM part, the time will be formatted as 24 hour format e.g., 19:00. The hh:mm inside the quotes after the cell reference in text function indicates the format. There are several possible combinations for several different formats.

We can then create the stated problem to be displayed by encoding the following in the merged cell.

="The distance between "&D3&" and "&F3&" is approximately "&B3&" km. If the bus left the bus station in "&D3&" at "&TEXT(D4,"h:mm AM/PM")&" and arrived in "&F3&" at "&TEXT(F4,"h:mm AM/PM")&", what is the average velocity of the bus?"

This is just the same as in our original spreadsheet problem generator. The only thing new here is the use of TEXT function to format the time. You should get something like this.

timevalue-spreadsheet-problem-final

Note that in the examples above, I also set Manila and Baguio as variables that you can edit. The reason here is that if you change the distance to something other than 250 km or any values close to it, Baguio and Manila may no longer apply.

Please review what we discussed in this section by downloading the following spreadsheets. Use them as guide in constructing your own spreadsheet problem generator: Time problem generator in Open Document Spreadsheet (Libre/OpenOffice).

If you wish to use some other format for your time in the TEXT function, please do the following:

  1. Format the cell reference to the time format that you want by right-clicking and opening the Format cells option window.
  2. time-problem-generator-select-cell

  3. Copy the Format code.
  4. time-problem-generator-format-code

  5. Go back to the TEXT function, delete the code inside the quotes and replace it with the Format code you just copied.

Please visit this post to know more about formatting time.

Constructing word problems that involves specific dates

Consider the following sample problem:

An oil mining facility is located 4800 km away from a major city. If an oil tanker left the mining facility on September 16, 2012 at 8:00 AM and arrived at the major city at September 26, 2012 at 6:00pm, what is the average velocity of the oil tanker.

First, we need to encode the given values. We will treat the dates and times as separate given values. Thus, we will have five given values. Encode them in your spreadsheet and use right-click to format them to whatever is convenient to you.

datevalue-spreadsheet-problem-given

Next, pick a blank cell and encode the following:

=(((DATEVALUE(TEXT(F3, "mm-dd-yyyy")) + (TIMEVALUE(TEXT(F4, "hh:mm"))) - (DATEVALUE(TEXT(D3, "mm-dd-yyyy"))+TIMEVALUE(TEXT(D4, "hh:mm"))))*24))

Here, F3 and F4 are the respectively the cell references for the date and time the oil tanker arrived at the major city, while D3 and D4 are the respectively the cell references for the date and time the oil tanker left the mining facility. Just replace them with the actual cells that you use in case they are different.

This is what this formula does:

  1. First, it gets the DATEVALUE and TIMEVALUE of the destination's date and time respectively, and then adds them to get the date serial of the moment the oil tanker arrived at its destination.
  2. Second, it gets the DATEVALUE and TIMEVALUE of the origin's date and time respectively, and then adds them to get the date serial of the moment the oil tanker left the mining facility.
  3. Finally, the date serial of origin is subtracted from the date serial of destination, and multiplied by 24 to get the actual number of hours.

Then, select another cell and divide the given distance with the computed number of hours using DATEVALUE and TIMEVALUE.

=B3/H3

You should get something like this.

datevalue-spreadsheet-problem-solution

Finally, merge some of the cells and use the & sign to concatenate text with data obtained from the cells to generate the word problem. Similar to time, you can format date using the TEXT function.

TEXT(F3,"mmmm d, yyyy")

This format displays the time in [full month name] [day number], [year] e.g., September 16, 2012. If you wish to use some other format for your date in the TEXT function, please do the following:

  1. Go to the cell reference (the F3 in example) and right-click on it to open the Format cells option window.
  2. Format the cell reference in the way that you want, possibly by selecting among the several possible formats for date.
  3. Copy the Format code.
  4. Go back to the TEXT function, delete the code inside the quotes and replace it with the Format code you just copied.

To know more about formatting date, please visit this post about formatting date and time.

We can then create the stated problem to be displayed by encoding the following in the merged cell.

="An oil mining facility is located "&B3&" km away from a major city. If an oil tanker left the mining facility on "&TEXT(D3,"mmmm d, yyyy")&" at "&TEXT(D4,"h:mm AM/PM")&" and arrived at the major city at "&TEXT(F3,"mmmm d, yyyy")&" at "&TEXT(F4,"h:mm AM/PM")&", what is the average velocity of the oil tanker."

The final spreadsheet would look something like this.

datevalue-spreadsheet-problem-final

Please review what we discussed in this section by downloading any of the following spreadsheets. Use them as guide in constructing your own spreadsheet problem generator: Date problem generator in Open Document Spreadsheet (Libre/OpenOffice).

Last updated on 19 Apr 2013. Tags: , , , ,

Learn from others
Nishat says:

This was the first real simple stlouion I have found. It actually took me 2 years to finally print a blank grid from open office Thank you One suggestion Click on grid box instead of ‘tick against grid’ Also it was a little fuzzy to me about the cursor in the cell Why not just say in upper left mark a cell then use space bar a few times then lower right. do the same!!Again I want to thank you folks Keep up the good work The -KISS- principle is the best :)

Greten says:

Sorry for the overly late reply as I was not able to do my blogging, been so busy with work (employment) and I was contemplating on bring this blog to a new direction. Anyway, I’m glad you find the information here useful.

Share your thoughts

* Required. Your email will never be displayed in public.

Free and open source software technology and internet usage guide for teachers and other professionals in the education sector