Spreadsheet problem generator part IV: formatting date and time

Posted by Greten on 20 Sep 2019 under Tools

In certain sciences such as Physics and Economics, you may encounter problems that have time as one of its variables. For example:

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 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 quickly determine how to solve this problem, the format the times were given is not something you can readily encode in a spreadsheet problem generator. Note that the values 11:00 AM and 7:00 PM are pointers of specific times of the day in a clock, and cannot be readily subjected to mathematical operations. This post will cover how you can mathematically operate on date and time using spreadsheet applications.

The codes and techniques that I will present here was tested on the version of Google Sheets at the time of posting (September 2019), as well as on MS Office Excel 2016 and LibreOffice Calc 6.3.

How spreadsheet records the date and time?

Date and time include several non-numerical variables such as months and AM/PM so it can be challenging to treat it as a number. However, spreadsheet applications store 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; in case of LibreOffice Calc and Google Sheets, it is December 30, 1899, which has a date serial of 0. Meaning, December 31, 1899 has a date serial of 1 and January 1, 1900 has date serial of 2. Dates earlier than December 30, 1899 has negative date serial. MS Excel has the same date serial as Calc and Sheets for March 1, 1900 and later, but not before this date.

The date serial allows us to treat date and time as a single number that can undergo mathematical operations. The count of date serial does not stop when the month or the year changes. For example, 1 January 2012 is 40,909; meaning 40,909 days have passed since 30 December 1899.

In date serial, the time within a day is the number on the right side of the decimal point. For example, 1 January 2012 at 12:00 PM in date serial is 40,909.5 since it is already halfway through the day. A given time, without any date, has a serial value between 0 and 1. For example, 3:00 AM has date serial of 0.125, and 6:00 PM has date serial of 0.75.

Using the TEXT function to obtain date serial

Part II of this post series introduced the TEXT function, which displays numerical data to a specific format. It is useful in getting data from one cell and then presenting it in a different format in another cell.

What is date serial anyway, but an alternative format of presenting date and time. You can convert a date or a time into a date serial by formatting it as @. The formula is like this:

=TEXT(<Cell>,"@")

If for example, the date is in cell C2, encode the following in the cell where you want to display the date serial.

=TEXT(C2,"@")

Recognizing the entry as date or time

For the date serial to appear using the TEXT function, the spreadsheet application must recognize that it is a date or a time. Here are some clues to look for to know if the spreadsheet application recognizes your cell entry as a date or a time.

• The entry automatically aligned to the right after the cell pointer left the cell where you made the entry, although you can format it to alight left or center later.
• You typed the date in any format you know, and spreadsheet reformats it. For example, in Excel, enter 23-4-2019, and it will change to 23/04/2019, or enter 23 April 2019, and it will change to 23-Apr-19.
• In the formula bar; the date is usually in the form of three numbers: day, month, and year, separated by slashes; if one date is formatted one way in the formula bar, it will have the same format throughout.

The format in the formula bar depends on your regional settings. I no longer want to dwell on how to change the regional settings as this can vary with your operating system.

In the above example, the cell shows a date format of dd-mmm-yy, but the formula bar shows mm/dd/yyyy. Among the three, Google Sheets seems to recognize the most number of date and time formats, while LibreOffice Calc recognizes the least. For example, Google Sheet recognizes "2:00pm" (lowercase and without space) as time, but in Excel there must be space before "pm" or it will render as regular text. Meanwhile, Calc cannot even recognize a standard date format that starts with the full name of the month and has a comma (e.g., September 1, 1998) as a valid date.

The format displayed by the cell is based on the cell format, which you can modify by right-clicking on the cell containing the date and selecting Format Cell. It will open a window like what is shown below.

This window is from Excel 2016. Calc has similar window. Sheets has no similar window but you can select Format on the top menu and then select Number.

Converting from date serial to common date and time formats

You can also reverse the date serial to a standard date format using the following codes:

• d - displays day number without trailing zero: 1, 2, 3..., 8, 9, 10, 11..., 31
• dd - displays day number with a trailing zero: 01, 02, 03..., 08, 09, 10, 11..., 31
• m - displays month number without trailing zero: 1, 2, 3..., 9, 10, 11, 12
• mm - displays month number with a trailing zero: 01, 02, 03..., 09, 10, 11, 12
• mmm - 3-letter abbreviation of a month: Jan, Feb, Mar, Apr...
• mmmm - full name of a month: January, February, March, April...
• yy - last two digits of the year: ....97, 98, 99, 00, 01, 02..., 17, 18, 19
• yyyy - year in four digits: ....1997, 1998, 1999, 2000, 2001, 2002..., 2017, 2018, 2019

Use the codes with the TEXT function inside the quotes:

=TEXT(<Cell>,"dd mmm yyyy")

You can rearrange the codes inside the quote, and insert other symbols commonly used in formatting a date such as hyphens and commas. For example, the date serial in cell C4 is 43723; the following formula will display the following date formats:

=TEXT(C4,"dd mmm yyyy")

=TEXT(C4,"m-dd-yyyy")

=TEXT(C4,"dd mmmm yy")

=TEXT(C4,"mmmm dd, yyyy")

For time, the codes for reversing the date serial are as follows:

• h - displays hour number without trailing zero: 1, 2, 3..., 10, 11, 12..., 23
• hh - displays hour number with trailing zero: 01, 02, 03..., 10, 11, 12..., 23
• m - displays minutes number without trailing zero: 1, 2, 3..., 9, 10, 11..., 59
• mm - displays minutes number with trailing zero: 01, 02, 03..., 09, 10, 11..., 59
• AM/PM - add this if you want to format the time as AM and PM; time defaults to 24-hour format if this is not present

For example, the date serial in cell J14 is 0.583333333333333. The following formulae will display the following time formats:

=TEXT(J14,"hh:mm")

=TEXT(J14,"hh:mm AM/PM")

=TEXT(J14,"h:mm AM/PM")

Constructing word problems that involves specific times

Using the Baguio-Manila example provided earlier. You 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 in the screen capture below.

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

=(TEXT(F4,"@") - TEXT(D4,"@"))*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. Replace the F4 and D4 with whatever cells you use to contain the time variables. What we did here is to get the date serial of each time, and then subtract the date serial of the latter time with the earlier time.

To get the actual number of hours, 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.

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

=B3/((TEXT(F4,"@") - TEXT(D4,"@"))*24)

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

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

This format displays the time in hour:minute format with AM or PM. Display the stated problem by encoding the following in the merged cell. This is a long and single line code so scroll rightward to see or copy everything.

="The distance between Manila and Baguio is approximately "&B3&" m. If a bus left Manila at "&TEXT(D4,"hh:mm AM/PM")&" and arrived at Baguio at "&TEXT(F4,"hh:mm AM/PM")&", what is the average velocity of that 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.

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.

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, you need to encode the given values. Treat the dates and times as separate given values. Thus, you have five given values. Encode them in your spreadsheet and use right-click to format them to whatever is convenient for you.

Next, pick a blank cell and encode the following:

=((TEXT(F3,"@")+TEXT(F4,"@"))-(TEXT(D3,"@")+TEXT(D4,"@")))*24

Here, F3 and F4 respectively are the cell references for the date and time the oil tanker arrived at the major city. Cells D3 and D4 respectively are 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 date serials of the destination's date and time, and then adds them to get the date serial of the moment the oil tanker arrived at its destination.
2. Second, it gets the date serials of the origin's date and time, 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.

=B3/H3

You should get something like this.

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. For example:

=TEXT(F3,"mmmm d, yyyy")

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?"

You can find the spreadsheets here:

• Google Sheets (link to Google drive)
• LibreOffice Calc (ODS file for download)
• MS Office Excel (XLSX file for download)

Difference among Calc, Sheets, and Excel

The TEXT function, as discussed here, works in practically the same way for Excel, Calc, and Sheets. However, there are a few notable difference.

• The three spreadsheet applications: Calc, Sheets, and Excel, round-off repeating decimals differently. For example, the time 2:00 PM has time serial with trailings 3s: 0.5833333333.... For Sheets and Excel, having eight trailing 3s, as in 0.5833333333, is sufficient to convert it to 2:00 PM. However, Calc converts it as 1:59 PM or 13:59. There must be twelve trailing 3s, as in 0.58333333333333, before Calc recognize it as 2:00 PM.
• Calc: has a format "general" that works the same way as "@".
• Excel: cannot display dates and date serial earlier than January 1, 1900, and shows incorrect date serial from January 1 to February 28, 1900. When you try to enter a date that is in the year 1899 or earlier, it turns into a regular text. The date serial for January 1, 1900 is 1, and the date serial for February 28, 1900 is 59. The error arose from the fact that Excel incorrectly assumed 1900 to be a leap year. Hence, it returns 60 as the date serial of the nonexistent February 29, 1900.
• Google Sheets: depending on your local setting (which you can see by selecting File » Spreadsheet Settings » Locale), you might need to use a semicolon instead of comma, like this:

  =Text(<Cell>;"@").

  Some local settings that should use semicolon are Spain or Germany, while the Philippines and the United States are among those that use comma. If you have a TEXT function that uses comma, and you change the local settings to a country that uses semicolon, all working functions will automatically change to semicolon, but any new TEXT functions you use should use semicolon. In the same manner, if you have working TEXT function in semicolons and you change the local to a country that uses comma, all semicolons in working TEXT function will change to comma, but you have to use comma for all new text functions.

This post is the last of the four-part post series. Here is the complete list of all the posts under this series.

• Spreadsheet problem generator part I: basic word problem generator
• Spreadsheet problem generator part II: units with special characters
• Spreadsheet problem generator part III: concatenate cells with special number format
• Spreadsheet problem generator part IV: formatting date and time

This post was formerly called part III, but the former part II splits into two. Hence, it also needs to be renumbered.

Last updated on 06 Oct 2019.

Learn from others

Nishat says:

September 22, 2014 at 7:20 am

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:

February 19, 2015 at 8:27 am

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.