Help with formula
 

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(DAT EDIF(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:D)/8,1)&" Days "&MOD(8-SUM(HList!D:D),8)&"
hours"

Help with formula
 

Richard,
My reading of the formulae is that A9 has days (whole
/half) and SUM(D: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: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:D))/8) &" Days " & MOD((A9*8-SUM(D:D)),8) &" Hours"

OR

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

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

HTH

"Richard" wrote:

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(DAT EDIF(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:D)/8,1)&" Days "&MOD(8-SUM(HList!D:D),8)&"
hours"

Help with formula
 

Yes!!! Thanks so much. Works perfect.

"Toppers" wrote:

Richard,
My reading of the formulae is that A9 has days (whole
/half) and SUM(D: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: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:D))/8) &" Days " & MOD((A9*8-SUM(D:D)),8) &" Hours"

OR

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

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

HTH

"Richard" wrote:

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(DAT EDIF(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:D)/8,1)&" Days "&MOD(8-SUM(HList!D:D),8)&"
hours"

Help with formula
 

Thank you for the feedback.

"Richard" wrote:

Yes!!! Thanks so much. Works perfect.

"Toppers" wrote:

Richard,
My reading of the formulae is that A9 has days (whole
/half) and SUM(D: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: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:D))/8) &" Days " & MOD((A9*8-SUM(D:D)),8) &" Hours"

OR

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

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

HTH

"Richard" wrote:

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(DAT EDIF(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:D)/8,1)&" Days "&MOD(8-SUM(HList!D:D),8)&"
hours"