Saturday, February 12, 2011

Extracting time from date in OpenOffice/LibreOffice Calc

If I have a spreadsheet cell that contains a date and time, formatted to display according to my preferences--say something like "Saturday, February 12, 2011 11:41:32", I can extract the time portion of that date for use in another cell. The secret to accomplishing this is understanding that date-times in OpenOffice Calc are stored as real numbers like this: "40586.4877". The integer part of the serial number is the number of days since 30 December 1899 (by default), and the decimal part is the fraction of the day that corresponds to the time. So I simply use a math function to extract the fraction, then format it as a time.

Like this:


The mod function simply is a remainder function. It gives the remainder (remember remainders from elementary school?) that results from dividing the number in A1 by 1. For example, 234.56 divided by 1 equals 234 with a remainder of 0.56. So mod(234.56,1) equals 0.56. And that's how our solution works. Happy calculating. :-)


Marco Gil said...

Very interesting... let me check!!

Thomas Gail Haws said...

Thanks, Marco.