Skip to content

Time Calculations in Excel

2008/09/11
by

Question

I have a column of cells in an Excel spreadsheet containing times like, 3:15, relating to the time spent on a task. At the bottom of the column I’ve used AutoSum to add up the times, but the result appears as either a date or funny number. What do I do to get a total in hours and minutes?

Answer

The formula you created with AutoSum is fine.

All you need do is apply an appropriate format to its result:

  • Select the cell containing the total
  • From the Format menu, choose the Cells command
  • From the Category list, select Custom
  • In the Type box, input this format [h]:mm (note the use of square brackets).
    If you want to show seconds in your total, input [h]:mm:ss (this format is listed in Excel’s Custom formats)
  • Click OK.
 

It sounds like your *times* are really text.  Try this on a Copy of you data:
Copy an unused cell then highlight the *times* and select Past Special > Add

I got:

20.41326
12.98186
6.893646

with your sample data.

Then reformat the cells as [H]:mm:ss (even if they were that format to start with)

It worked for me. 

http://www.eggheadcafe.com/software/aspnet/29923507/sum-time–strange-result.aspx

You can make a =SUM(YourRange) and then change the format to custom – [h]:mm:ss

If the times are stored as text, you can use: =SUMPRODUCT(–A1:A20) formatted as jpaulino suggested, but just note that it won’t break the minutes out into hours. (I assume from your question, you have no hours indicated)

>>What means "–" ? It’s a convertion to numeric ?
>>
>>I think I’m learning something new today :-) I love this!

As Rory indicates, that’s exactly what it is.  It’s sometimes referred to as "double unary".  Having just the one minus sign can force a type conversion and multiply the converted numeric value by -1.  The second minus sign multiplies again by -1, so the overall effect is to force a type conversion to a number and multiply be one (thus yielding itself).

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23014415.html

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=670

One Comment leave one →
  1. Jian Min permalink
    2008/09/21 7:39 PM

    Wooway, although I am not a sophisticated user of computer, I really do appreciate your tips for using it! Thank you very much!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: