How to Use Time in Formulas in Excel

Microsoft's Excel spreadsheet software is ubiquitous. Installed on virtually every office computer and on most newly purchased PCs, and fairly easy to use, Excel is the default spreadsheet program for most users. However, the program can be quite complex when it comes to functions and data formats, particularly those related to time. Simple arithmetic of hours and minutes may seem intuitive, but to get Excel to calculate your answers correctly, you need to know a few things. Fortunately, there are simple tips and steps that won't frustrate you or delay your work.

Things You'll Need

• Computer
• Microsoft Excel
• Moderate Excel experience

Instructions

Learning Time Formats

1. Make sure your data is in a time format. Highlight cells with your times, right-click, select the "Format Cells" option, and make sure you're using one of the provided time formats, such as one that shows "AM" or "PM."

2. Write your formula in a new cell. For example, if cell A1 is "4:00 AM" and cell B1 is "5:30 AM," to find the time difference, your would enter "=B1-A1" as your formula.

3. Set the formatting for cells containing your formula so that Excel will return a number of hours and minutes. Right-click and select the "Format Cells" option again.

4. Go to the Custom list of formats and select "h:mm" to return your answer in terms of hours and minutes. For example, the answer to the formula in Step 2 will be returned as "1:30."

Calculating Periods of Over 24 Hours

1. Set the formats to time formats for your input cells, as instructed in Section 1.

2. Enter your formula, adding an extra piece to make sure Excel understands that your time period goes beyond midnight. Using our example from Section 1, Step 2, you would enter "=B1-A1+IF(A1>B1, 1)" as your formula - adding the "+IF(A1>B1, 1)" part.

3. Set the format to a custom format again, but this time put the hours in brackets. To see your answer in hours and minutes, use "[h]:mm" as your format. In our example, our formula answer should return as "25:30."

Tips & Warnings

• Excel can also calculate time with seconds. Rather than just using the "h:mm" custom format, add ":ss" to the end so that your format is "h:mm:ss."

• This how-to applies to Excel 2007 and Excel 2003.

• Be sure to check your input data! If the formats aren't clear to Excel, it may define them as a format that won't work well in your formula. Be sure that they're set to an appropriate time format.

References

• Microsoft.com: How to Use Dates and Times in Excel
• OzGrid.com: Adding Excel Times Past 24 Hours