click here for frames page

Excel Time Calculation Sheet

My Excel Time Calculation Sheet

I created a spreadsheet for adding and subtracting times.  For pilots and schedulers, tt also calculates time in 7 days.  It does have one macro that clears one of the sheets.  If you can't load the macro for security reasons, or because you convert it to Palm or Pocket PC, the only thing affected is the CLEAR button on the first sheet.

Time Calculation Sheet (version 1.1) 5/8/2006

How Excel uses times

My workbook was meant to address some of the more commonly used pilot/scheduler time functions.  However, Excel can be a very versatile tool for manipulating times.  There are a variety of sources of information on how to use time in Excel, but here is a pretty good summary:  http://www.ozgrid.com/Excel/ExcelDateandTimes.htm.

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.

Other resources for manipulating times

There are several other ways of using time functions.  There are several time calculator programs for a variety of platforms (PC, Palm, Pocket PC, Mac, etc), there are some tricks for using an ordinary 4 function calculator, there are some dedicated calculators, and some business calculators with time functions.

The solution that often gets overlooked is the cheap scientific calculator.  They don't have time functions, but they will convert degrees, minutes, and seconds to decimal degrees and vice versa.  A time can be mathematically manipulated once an hour, minute, second (hh:mm:ss) is converted to decimal using the degree, minute, second function.  The results can then be converted back to a hh:mm:ss when you are finished.