Help with calculating HH:MM (resource) of DD:HH:MM (total job time)

[Waves2021]

Evening, i'm looking to calculate a cell which contains the total hours and minutes of a virtual worker resource can carry out. I then have a cell with the total days, hours and minutes it will take to do a job. I'm ideally looking to do two things, calculate how many resources I would need to do the job and total that up. I then would like to understand from the total virtual workforce needed to do all jobs what is the % of that needed for each job. I'm tying myself in knots and not sure whether this is around the formatting of cell types plus doing percentage of time against time. Any assistance would be very much appreciated.

 

[jasonb75]

One problem that stands out is an invalid number format.

dd:hh:mm is not a standard number format, it is not clear from the image if you have used a custom format or if the cells actually contain d hh:mm:ss with the day figure suppressed. Something there is wrong though, the total of 34.42 at the bottom doesn't match up to the values in the cells above it.

You need to set it up with some consistency and valid entries before you will be able to make any progress.

It would be preferable if you used XL2BB to post your samples so that the existing formatting and formulas are preserved and we can see exactly what you have rather than trying to guess from pictures.

There is a link to XL2BB in my signature at the bottom of this post which includes a detailed guide explaining how to use it.

 

[Dave Patton]

How did you get the numbers into column H?
Could you work with days and % of days instead of Time? See Column J.
The following shows the totals using separate Days and Time; check the numbers and arthmetic.

Please post your information with forum's XL2BB.

Date and Time 2021.xlsm
	H	I	J	K
1	Days	Hours	Hrs decimal
2	0	02:59	2.98
3	18	09:28	9.47
4	2	10:38	10.63
5	5	22:21	22.35
6	5	03:53	3.88
7	0	20:22	20.37
8	0	06:30	6.50
9	14	02:33	2.55
10	1	05:48	5.80
11	45	3:12:32	84.53
12	48	0:12:32	12.53	Hours
13			48	Days
14
3a

Cell Formulas
Range	Formula
H11:I11	H11	=SUM(H2:H10)
H12	H12	=H11+3
I12	I12	=I11-3
J2:J11	J2	=I2*24
J12	J12	=MOD(J11,24)
J13	J13	=INT(J11/24)+H11

 

[Waves2021]

Thank you for your replies jasonb75 & Dave Patton. I was able to get a hold of a colleague who was able to assist and changed as suggested above Dave. Much easier and was able to calculate the resource needed. Thanks again, have a good weekend.