calculate total time used formula

paul w

Board Regular
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

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Bo_Ry

Board Regular

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

paul w

Board Regular

=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

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

many thanks

paul

Phuoc

Active Member
Or try:

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

Replies
3
Views
94
Replies
0
Views
81
Replies
7
Views
111
Replies
2
Views
129
Replies
0
Views
478

1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back