Time formula for job

[craigwojo]

Hi everyone,

Cell P41 (formatted in TIME) 28:44:16 (hh:mm:ss) -INPUT cell
Need Cell Q41 to have the hours worked in a day - INPUT cell
Cell R41 (FORMULA cell) would take the two (2) cells and come up with the amount of days and time left over for the job.

example -

if P41 was 16:05:05 (total of time for job completion)
and Q41 was 8 (hours that will be work on it a day)
then the answer would be 2 days, 05 minutes, 05 seconds (The amount of days, with the reaming hours, minutes & seconds left over)

Any assistance will be appreciated.


Thank you,
Craig

[dcronje]

Will this satisfy your need:

=DAY(P41/Q41)&" day/s "&TEXT(MOD(P41,Q41),"HH:mm:ss")

this will work if Q41 is formatted as time i.e. 08:00

if you want to use just a number you can use this formula

=DAY(P41/(Q41/24))&" day/s "&TEXT(MOD(P41,(Q41/24)),"HH:mm:ss")

let me know

[craigwojo]

Thank you dcronje.

Thank you,
Craig

[craigwojo]

Was working with this formula and I've noticed an error in the answer.

In cell P41 it read 292:35:05
I input 8:00:00 (the format for the cell is [h]:mm:ss

Now the answer in cell (R41) is 5 day/s 04:35:05.

Shouldn't it be somewhere in the way of 36+ days?

Anyone?

Thank you,
Craig

[dcronje]

change "day" to "int"


=INT(P41/Q41)&" day/s "&TEXT(MOD(P41,Q41),"HH:mm:ss")

[craigwojo]

Thank you dcronje.

That is what I'm looking for.

Thank you,
Craig