calculate total time used formula

paul w

Board Regular
Joined
Apr 25, 2010
Messages
195
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
 

Some videos you may like

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
Joined
Oct 27, 2018
Messages
142
Office Version
  1. 365
Platform
  1. Windows
Please try

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

paul w

Board Regular
Joined
Apr 25, 2010
Messages
195
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

Board Regular
Joined
Apr 25, 2010
Messages
195
I managed to get it to work with the other sheets,

many thanks

paul
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top