Calc spreadsheet file used in video: Right-click to download file
Dates are stored as the number of days since 12/30/1899 you can see this if you type a date into a cell, such as 1/1/1900, then change the cell format to number by clicking the number button on the toolbar.
Because the dates are numbers they can be added and subtracted. For Example:
The result will give you the number of days elapsed since the Start Date, this does not include the Start Date, if you wanted the Start Date included in the calculation, then you would type +1 at the end of the formula: =B2-B1+1
The default date format is 12/31/99 with a 2 digit year. This can be changed by highlighting the cell or cells with dates then right-click and choose Format Cells. Under the Numbers tab click Date in the Category window, in the Format window there is a wide variety of date formats you can choose from, click on one then press OK.
There are several useful date functions available in Calc. To put the current date into a cell type =today() and press Enter. There are no parameters for this function. Whenever the spreadsheet is opened this will always show the current date.
The EOMONTH function returns the date of the last day of a month before or after a starting date, the first parameter is a start date, the second is the the number of months before or after the start date, thus a zero will give you the last day of the current month, -1 will give you the last day in the previous month and 1 will be the last day next month.
In the example I entered 02/01/2012 into A2 (2012 was a leap year), then into B2 I entered =EOMONTH(A1,0). You can type the date directly into the function but you must surround it in double quotes like this: =EOMONTH("2/1/2012",0). If you get a result like 40968, click the date format button.
The now() function, which has no parameters, gives you the date and time in the format 08/28/15 03:15 PM.
Calc stores an hour as 1/24 of a day, therefore 12:00 AM is stored as 0, 12:00 PM is 0.5 and the minutes and seconds are fractions of these numbers. In this way we can preform calculations on time. To see this in action type a time into a cell, for instance 6:00 PM then show it as a number by clicking the number format button.
It will show 0.75 which is 3/4 of a day.
Using this knowledge here's a way to calculate hours worked.
The cells A2 and B2 are formatted As Time, and Cell C2 is formatted as a number.
B2-A2 was put in parenthesis so that Calc will do the subtraction first, then the result is multiplied by 24 because time is stored as 1/24 of a day.
Here's an example of how to calculate the elapsed time between 2 events within a 24 hour period. The result will be in Hours, Minutes and seconds. First of all it's important to format the cells:
The formula basically breaks down to:
=(later<earlier)+later-earlier.
The (later<earlier) which is known as a Boolean equation, will return 0 if it's false, and 1 if it's true.
Because if the later is less then the earlier time it will return a negative value when it's subtracted from the earlier, so adding 1 makes it positive.