calculate total time used formula

[paul w]

hello all
its been a while , ive had my time sheet up and running for a few years now, many thanks to everyone on here that helped at the time,
the problem I have is I didn't notice it at the time, but I have a column for a time allowance, that is entered manualy eg, 1 hr 30 mins is entered as 0130,
I then custom formatted the cells to add a colon (00\:00) thinking this would add the time used' correctly, unfortunately
ive now just noticed if I add say 0130 then 0130 it calculates it as 0260 not 0300.
ive tried reformatted column as text, then using =TEXT(B10,"00\:00")+TEXT(B11,"00\:00")+TEXT(B12,"00\:00")+TEXT(B13,"00\:00")+TEXT(B14,"00\:00")+TEXT(B15,"00\:00") in the total cell, formatted to [hh]:mm
and this works, but my column is 31 rows and there are 3 sheets so 93 cells to total. im just wondering if there is an easier way.

many thanks
Paul

 

[Bo_Ry]

Please try

=sumproduct(dollarde(B10:B40/100,60))/24
and custom format to [h]:mm to get time format

 

[paul w]

Please try

=sumproduct(dollarde(B10:B40/100,60))/24
and custom format to [h]:mm to get time format
[/Q

Thank you so much for your quick reply, it worked a treat.
I've tried to have this total from the other sheets but getting the error "to many arguments for this function.
ive never seen the DOLLARDE function before so not sure how it functions.

=SUMPRODUCT(DOLLARDE(BN7:BN37,'Sheet 2'!BN7:BN37,'Sheet 3'!BN7:BN37/100,60))/24

again many thanks
paul

 

[paul w]

I managed to get it to work with the other sheets,

many thanks

paul

 

[Phuoc]

Or try:

=SUMPRODUCT(--TEXT(B10:B40,"00\:00"))