Date and time in OpenOffice are stored as numbers. Whole numbers represent dates and fractional numbers represent time. For example, six hours is stored as 0.25, representing one-fourth of a day. A number of 1.5 would represent 1 day, 12 hours. In addition, the OpenOffice function "hours(ref)" will extract the hours represented by any number and return it as an integer. The "time(h,m,s)" function will translate numbers into time values.
- Skill level:
Other People Are Reading
Enter time values in OpenOffice cells in the spreadsheet by using colon notation. Enter 5:00 to represent 5 hours. Enter 12:10:30 to represent 12 hours, 10 minutes, 30 seconds.
Add values by adding the numbers in the cells. Enter 6:00 in cell A1 and 12:10:30 in cell A2. Add them together in cell A3 with the formula =A1+A2. If the cells are formatted as time variables, the result will be 18:10:30. The same three cells, if they are formatted as numbers, would appear as 0.25, 0.50729167 and 0.75729167.
Extract the hour portion from any cell by using the "hour(ref)" function. Continuing our example, type "=hour(A3)" into cell A4 of the spreadsheet and OpenOffice will display 18, which is the sum of the hours only. Use the functions "=minutes(ref)" and "=seconds(ref)" to extract the minutes and seconds from any time number respectively. Once the hours have been extracted, add or subtract them in the same way as any other numbers.
Enter the function "=time(A4,0,0)" into cell A5 of the example spreadsheet. OpenOffice will display 18:00:00 as a time value. If this cell is formatted to display a number, it will be displayed as 0.75, or three-fourths of a day.
Select "Format -> Cells" from the menu and choose the Numbers tab to be sure the result is displayed as a time variable. Formatting hours values that are stored as numbers is the same as formatting regular numbers.
Tips and warnings
- Once hours exceed 24, the results will be displayed in days and hours.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for