Hours and Minutes

[BPHMA]

I am trying to keep track of the hours and minutes a machine has operated.
This can get up to 20,000 hours. How do I add daily operating hours to the
begining total?

[Bob Phillips]

A|re you storing the values of time and finding it can only go as far as 24?
If so, format the cell as [h]:mm

--

HTH

RP

"BPHMA" <[email protected]> wrote in message
news:[email protected]...
> I am trying to keep track of the hours and minutes a machine has operated.
> This can get up to 20,000 hours. How do I add daily operating hours to
the
> begining total?

[Pete]

I think it would be better keeping these as numeric, rather than in
Excel time format. You might have a cumulative total in column B, with
the daily total hours in A. In B2 this simple formula will give you
what you want:

=A2+B1

and this can be copied down as necessary. If A is derived from start
and end times somewhere else and these are stored in Excel date format,
you can convert to hours by multiplying by 24.

Hope this helps.

Pete

[BPHMA]

Bob,
I am using [hh]:mm. This works fine until I get to 10,000 hours, then
when I try to Sum cell a1 and a2 it doesn't work.

"Bob Phillips" wrote:

> A|re you storing the values of time and finding it can only go as far as 24?
> If so, format the cell as [h]:mm
>
> --
>
> HTH
>
> RP
>
> "BPHMA" <[email protected]> wrote in message
> news:[email protected]...
> > I am trying to keep track of the hours and minutes a machine has operated.
> > This can get up to 20,000 hours. How do I add daily operating hours to
> the
> > begining total?
>
>
>

[Bob Phillips]

Ah, yes. I remember this problem.

I found earlier that this was a problem, that it was an Excel oddity. I
found that as long as you keep below 10000
for the base number, you can continue adding it without problem.

This may not be ideal, but if you use something like a running total, it
should work. The trick is never to try adding a cell that is greater than
10000. As long as they arev all below, it adds and shows okay.


--

HTH

RP

"BPHMA" <[email protected]> wrote in message
news:[email protected]...
> Bob,
> I am using [hh]:mm. This works fine until I get to 10,000 hours, then
> when I try to Sum cell a1 and a2 it doesn't work.
>
> "Bob Phillips" wrote:
>
> > A|re you storing the values of time and finding it can only go as far as
24?
> > If so, format the cell as [h]:mm
> >
> > --
> >
> > HTH
> >
> > RP
> >
> > "BPHMA" <[email protected]> wrote in message
> > news:[email protected]...
> > > I am trying to keep track of the hours and minutes a machine has
operated.
> > > This can get up to 20,000 hours. How do I add daily operating hours
to
> > the
> > > begining total?
> >
> >
> >

[BPHMA]

Thanks for the information. I need to keep track of hours of operation and a
lot of the machines have over 10,000 hours. I guess the best way to do that
would be to keep number in numeric format and write a formula so that 60
minutes will equal one.

"Bob Phillips" wrote:

> Ah, yes. I remember this problem.
>
> I found earlier that this was a problem, that it was an Excel oddity. I
> found that as long as you keep below 10000
> for the base number, you can continue adding it without problem.
>
> This may not be ideal, but if you use something like a running total, it
> should work. The trick is never to try adding a cell that is greater than
> 10000. As long as they arev all below, it adds and shows okay.
>
>
> --
>
> HTH
>
> RP
>
> "BPHMA" <[email protected]> wrote in message
> news:[email protected]...
> > Bob,
> > I am using [hh]:mm. This works fine until I get to 10,000 hours, then
> > when I try to Sum cell a1 and a2 it doesn't work.
> >
> > "Bob Phillips" wrote:
> >
> > > A|re you storing the values of time and finding it can only go as far as
> 24?
> > > If so, format the cell as [h]:mm
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > >
> > > "BPHMA" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I am trying to keep track of the hours and minutes a machine has
> operated.
> > > > This can get up to 20,000 hours. How do I add daily operating hours
> to
> > > the
> > > > begining total?
> > >
> > >
> > >
>
>
>

[BPHMA]

I think something else would be need in the formula. Since 60 minutes equals
1 and it is different when adding decimal numbers.

"Pete" wrote:

> I think it would be better keeping these as numeric, rather than in
> Excel time format. You might have a cumulative total in column B, with
> the daily total hours in A. In B2 this simple formula will give you
> what you want:
>
> =A2+B1
>
> and this can be copied down as necessary. If A is derived from start
> and end times somewhere else and these are stored in Excel date format,
> you can convert to hours by multiplying by 24.
>
> Hope this helps.
>
> Pete
>
>

[daddylonglegs]

You can modify Pete's approach.

With your machine total so far in decimal hours in B1 and times to be added (in time format) in A2 down use

=B1+A2*24 formatted as general or number

copy down column

[BPHMA]

I can't seem to make this work. I have a machine with 12,300 hours and 23
minutes. I want to add daily operating time to this total. For example 3
hours 21 minutes. How do I do this in excel?

"daddylonglegs" wrote:

>
> You can modify Pete's approach.
>
> With your machine total so far in decimal hours in B1 and times to be
> added (in time format) in A2 down use
>
> =B1+A2*24 formatted as general or number
>
> copy down column
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=501979
>
>

[Pete]

You have to realise that if you store the hours as numeric, then 100.5
and 200.25 represent 100 hours 30 mins and 200 hours 15 mins
respectively. However, if you really want to show this as hours and
minutes, the following formula in C2 will do this:

=TEXT(INT(B2),"0")&" hrs "&TEXT((B2-INT(B2))*60,"0")&" mins"

Copy this down column C for as many values as you have in A and B. You
can then hide column B if this causes confusion.

Hope this helps.

Pete

[daddylonglegs]

BPHMA

perhaps its easiest to keep everything in hours and minutes - your only real issue is that you can't enter times 10,000 hours and above

..however you could get round this by showing your initial time of 12300:23 as a sum of two times under 10,000 hours e.g. in B1

="9000:00"+"3300:23"

this will give a result of 12:300:23 in B1 (format as [h]:mm)

and you can just add hours and minutes to this as necessary

Pete

this formula will do the same thing

=TEXT(B2/24,"[h] \hr\s m \mi\n\s")