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

Waves2021

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
Screenshot 2021-01-28 215733.png
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,992
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 15, 2002
Messages
4,974
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
HIJK
1DaysHoursHrs decimal
2002:592.98
31809:289.47
4210:3810.63
5522:2122.35
6503:533.88
7020:2220.37
8006:306.50
91402:332.55
10105:485.80
11453:12:3284.53
12480:12:3212.53Hours
1348Days
14
3a
Cell Formulas
RangeFormula
H11:I11H11=SUM(H2:H10)
H12H12=H11+3
I12I12=I11-3
J2:J11J2=I2*24
J12J12=MOD(J11,24)
J13J13=INT(J11/24)+H11
 
Solution

Waves2021

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,144,629
Messages
5,725,390
Members
422,623
Latest member
Dave52

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