peoples-stories

Some of my quick tricks for using times in Excel

I recommend that you read http://www.ozgrid.com/Excel/ExcelDateandTimes.htm to get a quick overview of how times work in Excel. 

Most simple things like adding, subtracting, multiplying, and dividing can be used by just formatting the effected cells.  For example, if you want to find the difference between times in cells A1 and A2, just enter "=A1-A2" into A3.  Select the cells and use Format -> Cells -> Number tab to select a suitable time format.   If you want to format a cell which has a time greater than 24 hours, you can find [h]:mm under Custom on the Number tab.  For example, if you have a sum of times you would probably prefer to see 75:30 rather than 1/3/1900 3:30 AM.  By the way, if you only find [h]:mm:ss, you can delete the :ss in the format tab to only show [h]:mm.

Using times in formulas can be very tricky.  However, using cells in formulas that contain time is usually just a matter of formatting.  For example, if I want to divide 75 hours by 30 days I can't just put "=75/30" into a cell and format the results.  I could divide the 75 by 24 to convert it into days.  Thus, I could put "=(75/24)/30" into the cell.  One of the easier ways that I have found to deal with these problems is to put 75:00 into a cell and then use the cell in the formula.  In this case the result would be something like "=A1/30" in a cell formatted for [h]:mm.