You are here:

Excel/Calculating and summing military time

Advertisement


Question
I have the following data in these cells

A1 07:45  (this represents the time i arrived at work)
A2 15:45  (this represents the time I leave work
b1 A2-A1+(A2>A1)  This shows that I worked 8 hours, displayed as 8.00

I have this formula and date for each day of the work.

How do I sum the daily number of hours?  When I add the individual hours for each day via either a sum function, or b1+d1, etc., I would expect to get 40 for the sum having worked 8 hours per day, however it is adding to 16.

Answer
In b1, you need:

8.00


Because, in Excel, a 1 is 1 day.

I can't figure out why it shows 16, but if you change it to a NUMBER format instead of time, you'll get 1.67, which is 1.67 DAYS. To convert it to hours, you must multiply by 24.

Chip Pearson has a great explanation of all this.

http://www.cpearson.com/excel/datetime.htm
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Anne Troy

Expertise

Virtually all common questions about Microsoft Excel, including all versions. Help with formulas, functions, optimum data layout, and application management. Please: No VBA or macro questions.

Experience

I taught Advanced Microsoft Excel to the senior accountants at the Environmental Protection Agency. Let's hope they were listening.

Publications
Dreamboat on Word Office VBA: Macros You Can Use Today

Education/Credentials
Self-taught and worked with the best in Microsoft Office applications.

Awards and Honors
Microsoft Word MVP 2008

©2016 About.com. All rights reserved.