Excel To Count hours more than 10000 hours

[thunder_anger]

Dear all

i got help yesterday from MrKowz that saved the day for me
but today there is a question, i know that it is silly but for sure i do not know how to do it

i am dealing with working hours for the annual report that must contain the total hours of work of about 5000 workers all over EGYPT in the branches of the firm i work for
so when i write this value (11253:52) Excel put it as text not like (5559:32) it recognize it as formatted time ([h]:mm)

what shall i do now

 

[WaterGypsy]

Have you tried putting a single quote before the value to force it to be text ?

 

[thunder_anger]

no sir

i want excel to recognize the hours more than 10000 as a time
e.g

11253:32

is 11253 hours and 32 minutes

 

[WaterGypsy]

I'm only having a stab in the dark but maybe if you entered 11253:32:00 it might force excel to recognise it as time.

BUT I would have thought that if you are reporting that many hours that the minutes would be pretty immaterial and you could simply show the hours but I don't know how fussy your end-users are

 

[WaterGypsy]

On the other hand ... Excel treats time as a special type of variable and if you add 12 hours to 13 hours you don't get 25 hours you get 1 day and 1 hour ...

.... you may have to actually work with minutes and adjust how you display the result

 

[barry houdini]

Excel doesn't recognise time values of 10000:00:00 or over although, as long as the inputs are below that, you can get a formula to return greater values that are valid time values.

To solve your problem you may need to split the times into amounts that are lower than 10000:00:00, whether you can do that possibly depends on exactly what you are doing, can you explain what you are trying to achieve?

 

[thunder_anger]

To solve your problem you may need to split the times into amounts that are lower than 10000:00:00, whether you can do that possibly depends on exactly what you are doing, can you explain what you are trying to achieve?

All waht i want to do is the following
in cells from A12:A150 i will enter the times that are printed out on papers
11252:56
19652:30
11859:26
21292:56
5226:17
26335:05
11252:10
6652:56
2552:59
etc
all of these are times that our employees have in about 2 years
the purpose behind that is to calculate the cost of every our according to the employee rank and work
but when i came to sum all previous values i couldn't because excel can not calculate after 9999:59:59
any ideas????!!!!!

 

[GlennUK]

It looks like you can get Excel to convert those to time values, and then use those in sums etc ... like this:

Excel Workbook
	A	B	C	D
1	Entries	Conv 1	Conv2	As a decimal
2	11252:56	11252	11252:56:00	11,252.93
3	19652:30	19652	19652:30:00	19,652.50
4	11859:26	11859	11859:26:00	11,859.43
5	21292:56	21292	21292:56:00	21,292.93
6	5226:17:00	#VALUE!	5226:17:00	5,226.28
7	26335:05	26335	26335:05:00	26,335.08
8	11252:10	11252	11252:10:00	11,252.17
9	6652:56:00	#VALUE!	6652:56:00	6,652.93
10	2552:59:00	#VALUE!	2552:59:00	2,552.98
11		Sum =	116077:15:00	116,077.25
12			check sum =	116,077.25
Sheet3

I've done it in 2 columns so that you can see what I'm aiming at.

 

[barry houdini]

You could use a single formula like this

=SUM(IF(ISNUMBER(A12:A150+0),A12:A150,RIGHT(A12:A150,7)+LEFT(A12:A150,LEN(A12)-7)*10000/24))

confirmed with CTRL+SHIFT+ENTER

format the result cell as [h]:mm

 

[thunder_anger]

thanks GlennUK

it works

but this solution is Easier
works like a charm

You could use a single formula like this

=SUM(IF(ISNUMBER(A12:A150+0),A12:A150,RIGHT(A12:A150,7)+LEFT(A12:A150,LEN(A12)-7)*10000/24))

confirmed with CTRL+SHIFT+ENTER

format the result cell as [h]:mm