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

 

[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.

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

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

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.

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

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.

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

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.

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

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.

 

[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?

 

[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")