OpenOffice/LibreOffice

Calc

Tips

1. If cells contain numbers that start with an apostrophe ('), then things such as aggregate functions in pivot tables will not work, even if the cells are formatted as "Number." To remove the apostrophes: Edit > Find & Replace > Search for = ".*", Replace = "&", More Options > Check "Regular Expressions"

Dates/Times

Dates and times are interpreted in terms of days, so if you want to add or a subtract a numeric amount of time to a value, convert the numeric value to days first. For example, let's say A1 holds a time (e.g. 07:34:53.123) and let's say you want to add 100 milliseconds to it. Let's say the 100 is in cell A2. The equation would be =A1+(A2/(1000*60*60*24))

To convert a text value of a date/time in ISO8601 format:

=DATE(MID(A2, 1, 4), MID(A2, 6, 2), MID(A2, 9, 2))+(MID(A2, 12, 2)/24)+(MID(A2, 15, 2)/(60*24))+(MID(A2, 18, 2)/(60*60*24))+(MID(A2, 21, 3)/(1000*60*60*24))

Truncate milliseconds from a time: =TIME(HOUR(A1); MINUTE(A2); SECOND(A2))

Previous Section (Wily Introscope) | Next Section (Acronyms) | Back to Table of Contents