Help with formula

[Guest]

Sometimes A4 has less than a whole number such as .5 The .5 is actually 4
hours.
This formula works fine except when there ia a half day.
Here is the formula that sometimes has a half day
"A4"=IF(DATEDIF(B16,TODAY(),"Y")>11,20,VLOOKUP(DATEDIF(B16,TODAY(),"Y"),K:L,2,0))

"A9"=A4+B3+F4+F5+F6

How can I get this formula to read like (1 Days 4 hours), It now reads
like(1.5 days 0 hours)
Here is the formula giving me the problem
"A12" =A9-CEILING(SUM(HList!D)/8,1)&" Days "&MOD(8-SUM(HList!D),8)&"
hours"

 

[Guest]

Richard,
My reading of the formulae is that A9 has days (whole
/half) and SUM(D) has hours, and a day is 8 hours.

If this is correct, perhaps something like this is what is required i.e.
Convert the days to hours, subtract the SUM(D) hours and then convert
result to days /hours. If you want results to nearest whole/half day then you
will need to adjust the formula.

=INT((A9*8-SUM(D))/8) &" Days " & MOD((A9*8-SUM(D)),8) &" Hours"

OR

=INT(CEILING((A9*8-SUM(D)),4)/8) &" Days " &
MOD(CEILING((A9*8-SUM(D)),4),8) &" Hours"

Your current formual in A12 effectively only calculates days/hours on
SUM(D) and "ignores" A9.

HTH

 

[Guest]

Yes!!! Thanks so much. Works perfect.

Richard,
My reading of the formulae is that A9 has days (whole
/half) and SUM(D) has hours, and a day is 8 hours.

If this is correct, perhaps something like this is what is required i.e.
Convert the days to hours, subtract the SUM(D) hours and then convert
result to days /hours. If you want results to nearest whole/half day then you
will need to adjust the formula.

=INT((A9*8-SUM(D))/8) &" Days " & MOD((A9*8-SUM(D)),8) &" Hours"

OR

=INT(CEILING((A9*8-SUM(D)),4)/8) &" Days " &
MOD(CEILING((A9*8-SUM(D)),4),8) &" Hours"

Your current formual in A12 effectively only calculates days/hours on
SUM(D) and "ignores" A9.

HTH

 

[Guest]

Thank you for the feedback.

Yes!!! Thanks so much. Works perfect.