Prev: Summing Data based upon a condition
Next: Excel 2003 formula
From: Roger D Roger on 8 Apr 2010 14:09
I want to enter a column of data as time DURATION (not clock time) in mm:ss
format and then sum the total. What cell format should I select for the
duration entries (mm:ss does not work) and what calculation steps do I need
to use to sum those durations?
From: Russell Dawson on 8 Apr 2010 14:50
Hi Roger

Try this.

The format should be [mm]:ss

Entry must be as 0:00:00

Use sum function to calculate total.
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Roger D" wrote:

> I want to enter a column of data as time DURATION (not clock time) in mm:ss
> format and then sum the total. What cell format should I select for the
> duration entries (mm:ss does not work) and what calculation steps do I need
> to use to sum those durations?
From: Russell Dawson on 8 Apr 2010 14:59
Your total can be hh:mm:ss if you want.
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Russell Dawson" wrote:

> Hi Roger
>
> Try this.
>
> The format should be [mm]:ss
>
> Entry must be as 0:00:00
>
> Use sum function to calculate total.
> --
> Russell Dawson
> Excel Student
>
> Please hit "Yes" if this post was helpful.
>
>
> "Roger D" wrote:
>
> > I want to enter a column of data as time DURATION (not clock time) in mm:ss
> > format and then sum the total. What cell format should I select for the
> > duration entries (mm:ss does not work) and what calculation steps do I need
> > to use to sum those durations?
From: Ron Rosenfeld on 8 Apr 2010 19:24
On Thu, 8 Apr 2010 11:09:02 -0700, Roger D <Roger D(a)discussions.microsoft.com>
wrote:

>I want to enter a column of data as time DURATION (not clock time) in mm:ss
>format and then sum the total. What cell format should I select for the
>duration entries (mm:ss does not work) and what calculation steps do I need
>to use to sum those durations?

A common misconception, with regard to dates and times, is that the cell format
has something to do with how Excel parses the input.

It does not.

If you input 55:33, Excel will interpret that as 55 hrs 33 minutes no matter
what format you have set. (Well, if you have set text, it will be interpreted
as a text string, but certain function will convert it to hrs:minutes.

If you want to input 55 min 33 sec, you'll need to enter 0:55:33. In other
words, enter your value in h:m:s or h:mm:ss format.

Some alternatives:
If you are always going to enter your data as m:ss, in an adjacent column
enter the formula
=cell_ref/60
This will convert your entry to m:ss, but won't work if you are also entering
fractions of a second.

Use a VBA macro to do the same thing.

If you are entering fractions of a second, ALL THE TIME, it might work. But
you'll have to remember to add the trailing zero.

In other words:

ENTER
55:33 --> 55 hours, 33 minutes
55:33.0 --> 55 minutes, 33 seconds





--ron
 | 
Pages: 1
Prev: Summing Data based upon a condition
Next: Excel 2003 formula