2
0

1
0
0

timeadding

Have you ever tried to add up hours in Excel? It’s kind of tricky…

For all of its intelligence, when it comes to adding up time units, Excel can’t guess how you want to track hours or how to display them, leaving you googling for solutions and voila! You come to this blog (we provide the top ranked accounting software in the USA for inventory-centric businesses that run on Mac/Apple networks).

Often, you end up entering time in hours and minutes and then end up with a total in hours and tenths of hours (i.e. 10.3 hours) that may not add up as you expected. This is especially common when your hours exceed 24 since, by default, Excel reports hour totals in days.

Before You Start: Data Input Considerations

The following steps will ensure that MS Excel on PC tracks time how you would like it to:

1. For entering hours, enter all your hours and minutes in a format that Excel likes

  • hh:mm                       5:15               would be 5 hours and 15 minutes

  • hh:mm:ss                  5:15:25          would be 5 hours, 15 minutes and 25 seconds

2. For entering the times work was started and ended, use a format like this

      • hh:mm AM/PM          5:15 PM

    1. Select and right click on the time entry cells

    2. Click Format Cells

    3. Click the Number tab

    4. Select Time and choose your preferred display “Type”

Excel Hours Snip - Time Format

3. When calculating hours worked and totaling your hours there are two methods you can use; the first produces totals in hours and minutes and the second produces totals in hours and tenths

 

Method 1 (This method will display your total as hours and minutes)

  • Right click on the cell with your calculated hours

  • Click Format Cells

  • Click the Number tab

  • Select “Custom” and select the [h]:mm setting (the brackets around the [h] are key here)

  • In the following example, your calculated hours cells as well as your total hours cell will need to be formatted this way.

Excel Hours Snip - Time Format

Method 2 (This method will display your total as hours and tenths)

  • Right click on the cell with your calculated hours

  • Click Format Cells

  • Click the Number tab

  • Select “Number” and indicate 2 decimal places

  • In the formula bar for your calculated hours add *24 to the end of the formula (e.g. =(G8-E8-F8)*24)

  • Note: in the following example the total sum of the hours column should not be multiplied by 24 (since your calculated hours already do this) but should be in the “Number” format.

 Excel Hours Snip - Method 2

 

With these steps and some practice, you should be able to put together a functional time-tracking worksheet. If this is not for you, time-tracking templates can be found for Excel on the web for free.

Better yet, small to medium companies with inventory and any complexity would do well to consider a full Enterprise Resource Planning (ERP) system that automatically combines human resource functionality with accounting, manufacturing, sales, distribution and inventory modules. Of course our suggestion would be to check out AcctVantage! If you would like to find out if ERP is right for your business, give us a call. A real person who knows the software will answer and we’ll discuss your business in detail.