Formatting cell data in Calc part V: date and time
Posted by Greten on 13 Apr 2013 under Tools
This post assumes that you already selected the cell(s) that you want to format and already opened the Format Cells option window. If not, please visit this prerequisite post on cell formatting.
Date and time are entirely different kind of data from the number, fraction, percentage, scientific and currency as they do not represent an amount of anything. Instead they pinpoint to a specific point in time. If we say 2 hours, then we are talking about an amount, a length of time. However, if we say 2:00AM, then we are talking about a specific instant of time within a day. For that, we normally cannot subject date and time to mathematical computations, but there are ways to do it depending on the kind of problem that you have.
The default format of date in the cell is MM/DD/YY. M stands for month, D for day and Y for year.
Having two of each letters means each is represented by two digits. Thus, January to September is shown as 01 to 09, with October to December already having two digits (10 to 12), thus no need for leading zero. The first to ninth day of the month also have leading zeroes. The year, on the other hand, since only two digits are allocated, the numbers that pertain to the century is removed.
For example, March 25, 1971 is shown in the default format as 03/25/71 and October 9, 2002 is shown as 10/09/02.
The separators that the default format used is slash (/) but there's no restriction on what separator to use (except those that represent the numerical values of the date like the aforementioned Y's, D's and M's). Aside from slash, other commonly used separators in dates are dash (-), dot(.) or comma(,). The comma is usually used if we write the month in letters instead of numbers.
There are different ways to format the days, months and years based on the number of letters that you use.
- YY - year without century e.g., 02, 98
- YYYY - year with century e.g., 2002, 1998
- There's no Y or YYY. Calc would not intepret them as year
- M - month in numerical without leading zero e.g., 1 to 9 for January to September and 10 to 11 for October to December
- MM - month in numerical with leading zero e.g., 01 to 09 for January to September and 10 to 11 for October to December
- MMM - month in three letter abbreviation e.g., Jan, Feb, Mar, Apr
- MMMM - month in full word e.g, January, December
- D - day without leading zero e.g., 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,..., 31
- DD - day with leading zero e.g., 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11..., 31
Using these characters in the Format code, there are actually many ways you can format the date. You can use different characters or symbols as separators and rearrange them. Some examples are shown below:
- D MMMM YYYY — 2 December 1994
- MM-DD-YYYY — 12-02-1994
- MMM D, YYYY — Dec 2, 1994
- YY MMM DD — 94 Dec 02
In the input line, the format is always MM/DD/YYYY. This is similar to default except that the year is in full four digits. You cannot change the format in input line and you can edit only this format. However, if you do not like recalling which number corresponds to which month, you can just delete the data in the entire cell and encode the date the way you prefer it.
There are other characters that you can add in your date Format code, but I do not see now they can be useful in spreadsheet problem generator so I'm presenting them here just for informational purpose.
- NN — weekday in three letter abbreviation e.g., Sun, Mon, Tue,..., Sat
- NNN — weekday in full name e.g., Sunday, Monday,..., Saturday
- NNNN — weekday in full name followed by comma, just the same as "NNN,"
- Q — quarter in the form of Q1, Q2, Q3, Q4.
- QQ — quarter in the form of 1st Quarter, 4th Quarter.
Other letters used in the code seems to be messy in LibreOffice 220.127.116.11 which I am using as I wrote this post. E is supposedly number of year within an era, but for some reason it translates the date in Hebrew. GG is era (as in BC and AD) in abbreviation, which works fine. GGG is supposed to be era in full but it stills display BC and AD.
Note that even though our examples thoroughly used capital letters for month, day, year, etc. in the Format code, encoding small letters are accepted by Calc. You can type in the format code mmmm d, yyyy, the cell will still display December 2, 1994. However, once you left the Format Cells option window by clicking the OK button, then you accessed it again, you will see that Calc has changed it to capital letters MMMM D, YYYY.
The default format for time is HH:MM:SS AM/PM. This is also the format in which it appears in the input line. If you encoded something like 2:35, Calc will automatically format it as 2:35:00 AM and this is also what will appear in input line. If you encoded 23:15, both the cell and the input line will display 11:15:00 PM. Just like in date, number and all other numerical data, you cannot change the way time is displayed in the input line.
There are four codes that Calc used in contolling the time format:
- H - displays hour, no leading zero, e.g., 1, 2, 3,..., 11, 12
- HH - displays hour with leading zero for one-digit numbers, e.g., 01, 02, 03,..., 11, 12
- M - displays minutes, no leading zero, e.g., 0, 1, 2, 3,..., 57, 58, 59
- MM - displays minutes with leading zero for one-digit numbers, e.g., 00, 01, 02, 03,..., 57, 58, 59
- S - displays minutes, no leading zero, e.g., 0, 1, 2, 3,..., 57, 58, 59
- SS - displays seconds with leading zero for one-digit numbers, e.g., 00, 01, 02, 03,..., 57, 58, 59
- AM/PM - put this at the right end of the Format code and Calc will append AM or PM as needed. Without this, Calc will use the 24 hour format, e.g, 16:00 instead of 4:00 PM.
Unlike the date's day, month and year, you cannot rearrange the hour, minutes and seconds in any order that you want. Minute should always be at the right of hour, and second is either at the right of minute or absent.
The colon(:), even though it's what triggers Calc to format the data as time, is not really a requirement. You can use any character or symbols as separator. However, the most common aside from colon is the "no separator" format. For example, 4:30 is shown as 0430.
Using these characters in the Format code, there are many ways you can format the time. Some examples are shown below:
- H:MM AM/PM - 3:00 AM, 11:00 PM
- HH:MM AM/PM - 03:00 AM, 11:00 PM
- H:MM:SS AM/PM - 3:25:47 PM
- HH:MM:SS - 02:23:12, 15:25:47
Similar to date, you can encode the Format code using small letters. You can even encode AM/PM as am/pm. But then, just like date, when you leave the Format Cells option window, and access it again, the Format code are all replaced by Calc with capital letters.
Combined date and time in one cell
It is possible to combine the date and time into one cell. For example, it is possible to type something like:
December 12, 2011 5:00PM
Calc can immediately detect it and format it as:
12/23/11 05:00 PM
However, unlike in the time only cell, the format of the time part as seen in the input line is the 24 hour format. Meaning, what we see in the cell as 12/23/11 05:00 PM will be seen as 12/23/2011 17:00:00 as seen in the input line.
You can format the date-time combination using the same Format code that we used in date and time. The only thing that we need to watch is the M because it denotes both month and minute. Calc makes a distinction that any M in the immediate right of H is regarded as minute. Anywhere else, then it's considered a month. Consider the following examples:
- D MMMM YYYY H:MM AM/PM - 17 February 1998 6:25 PM
- D MMMM YYYY H AM/PM MM - 17 February 1998 6 PM 02
You may open your Calc, whether it's LibreOffice or OpenOffice and test these examples. You will notice that if we moved the minute part on the right of AM/PM, it's no longer recognized as a minute but as month. Thus it displays 02 as how the MM format will display February.
Last updated on 03 May 2013.
Share your thoughts
* Required. Your email will never be displayed in public.