Adding hours and minute more than 10000 hours

MARK858

MrExcel MVP

Joined

Nov 12, 2010

Messages

16,167

Office Version

1. 365

Platform

1. Windows
2. Mobile

• Feb 23, 2017

• #2

Do the formula as you have written it then custom format the cell as [h]:mm.

 

Upvote 0

S

seekerarcane

Board Regular

Joined

Dec 18, 2016

Messages

104

• Feb 23, 2017

• #3

Formula is correct, just change the format of your formula cell as you desire.

 

Upvote 0

A

abatheo

New Member

Joined

Feb 23, 2017

Messages

3

• Feb 23, 2017

• #4

I selected h:mm not working
it shows #VALUE!

 

Upvote 0

L

liveinhope

Well-known Member

Joined

Dec 16, 2013

Messages

857

• Feb 23, 2017

• #5

you need this format [h]:mm note the square brackets round the 'h"

 

Upvote 0

A

abatheo

New Member

Joined

Feb 23, 2017

Messages

3

• Feb 23, 2017

• #6

From 0-9999:59 it works
the problem is from 10000:00 hours

 

Upvote 0

L

liveinhope

Well-known Member

Joined

Dec 16, 2013

Messages

857

• Feb 23, 2017

• #7

the problem seems to be that if you enter 10000:00 hours directly in the cell as a value excel interprets that as text (formula = =TYPE(A2)

hours	tyoe
9999:59:59	1
10000:00	2

<tbody>
</tbody>

However if you have a value more than 10,000 hours that is a result of a formula excel continues to regard it is a number so you add up
in this example the hours are pretty big ..
formula for a3 is = A2*2 (a4 = a3*2, a5 = a4 * 2 , etc,etc)

hours	data type		total hrs
9999:59:59	1		10229999:42:57
19999:59:58	1
39999:59:56	1
79999:59:52	1
159999:59:44	1
319999:59:28	1
639999:58:56	1
1279999:57:52	1
2559999:55:44	1
5119999:51:28	1

<tbody>
</tbody>

 

Last edited: Feb 23, 2017

Upvote 0

L

liveinhope

Well-known Member

Joined

Dec 16, 2013

Messages

857

• Feb 23, 2017

• #8

Another funny thing I found is that if I copy-and -paste the formulas as values excel treats those values a numbers too so you can add, etc

hours (formula)	data type		total hrs		hours (col a copy-and-pasted)	type		total hrs
9999:59:59	1		10229999:42:57		9999:59:59	1		10229999:42:57
19999:59:58	1				19999:59:58	1
39999:59:56	1				39999:59:56	1
79999:59:52	1				79999:59:52	1
159999:59:44	1				159999:59:44	1
319999:59:28	1				319999:59:28	1
639999:58:56	1				639999:58:56	1
1279999:57:52	1				1279999:57:52	1
2559999:55:44	1				2559999:55:44	1
5119999:51:28	1				5119999:51:28	1

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

Upvote 0

mole999

Well-known Member

Joined

Oct 23, 2004

Messages

10,524

Office Version

1. 2019
2. 2016
3. 2013

Platform

1. Windows

• Feb 23, 2017

• #9

there is a maximal value for [h]

 

Upvote 0

L

liveinhope

Well-known Member

Joined

Dec 16, 2013

Messages

857

• Feb 23, 2017

• #10

mole999 said:

there is a maximal value for [h]

Click to expand...

the limit of 10,000 doesn't apply if it's a result of a formula or copy-and-paste (see my post above)
only seems to be a problem if you enter 10000:00 into a cell

maybe abatheo can get excel to accept their >= 10,000 hour entries

 

Upvote 0