Re: HELP--- adding duration of times

Tech-Archive recommends: Fix windows errors by optimizing your registry

If you are trying to display hours and minutes beyond 24 hours, you need to
format the cells as [h]:mm, not as h:mm, and thus the 12 numbers you quoted
will sum to 37:50.
In multiplying by 24 you get 37.833333, which is the value in number of
hours (not in Excel time format), which you should be formatting as General
or Number. You seem to have multiplied by 24 but still tried to display as
if it were an Excel time.
37.833333 is equivalent to 37 days and 20 hours, so in displaying as a time
you are seeing the 20 hours as 20:00.
--
David Biddulph

"David Teich" <DavidTeich@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A9F5096D-5572-438F-B862-CC7FE16DAC54@xxxxxxxxxxxxxxxx
That doesn't work for me. I'm using the help file's SUM(A2:A6)*24 because
it's more than 24 hours. I'm adding:

4:05
7:00
1:20
6:45
4:30
1:05
1:55
5:15
0:15
1:45
3:20
0:35

and I'm getting 20:00 as the result, which is obviously wrong. Why does
the
summation not work?

"Peo Sjoblom" wrote:

It's correct, it is whomever entered times as :mm:ss (leading colon) that
made a mistake. Excel interprets this as text, so change the :42:39 to
0:42:39 and all the other entries that were made like that the same way
and
you should get what you expect


--
Regards,

Peo Sjoblom



"Barbara W" <BarbaraW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2A746501-66C0-40C1-9C6F-F8A19EDF24ED@xxxxxxxxxxxxxxxx
Thanx for the quick response. However, I tried using that format and it
is
not adding up.

Here's what is happening:

1:03:06
:42:39
1:07:10
:55:54
:09:49
1:17:18
The sum is 3:27:34
--
which is incorrect it should be 5:15:56.

Please respond...
Barbara W


"Piscator" wrote:

You didn't say what was appearing but yes, you probably need to format
the total cell.
Format, Cell, Number, Time and select the most appropriate. Mine has
a 37:30:55 option which should be OK for you.







.

Relevant Pages

  • Re: Sum,Sort, Format, and Date Range
    ... it is best to let query's get the raw data & let the display ... Sum([COST DIECOST]) AS ... Format property, ...
    (microsoft.public.access.queries)
  • Re: HELP--- adding duration of times
    ... "David Biddulph" wrote: ... will sum to 37:50. ... In multiplying by 24 you get 37.833333, which is the value in number of ... Format, Cell, Number, Time and select the most appropriate. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Adding and Subtracting Times
    ... minutes, and sum that. ... h:mm format, write your own function to convert from minutes to h:mm. ... history of start time and end times. ... display the total in Hours and minutes format. ...
    (microsoft.public.access.queries)
  • Re: time adding in excell
    ... The problem here is that by multiplying the sum by 24, ... Solution, format the result cell as number, or leave it as time and don't ...
    (microsoft.public.excel.misc)
  • Re: time adding in excell
    ... >The problem here is that by multiplying the sum by 24, ... >format defaults to time. ... >Solution, format the result cell as number, or leave it ...
    (microsoft.public.excel.misc)