#" Days" ?/?" Hours"

Special-K

Board Regular
My results aren't displaying the way I had hoped ...
So, perhaps a kind-hearted Excel expert can guide me to a solution?

=SUM(AE6/G111+AG6)

And the formatting of the cell is:
CUSTOM formatting
#" Days" ?/?" Hours"

Now this is how it's displaying:
Say the calculation AE6/G111= 1 .... and the fraction in cell AG6=0
Then my result will show:
1 [ SPACE ] Hours

BUT ... (with the same calculation) ... say the calculation AE6/G111= 1 ... and the fraction in cell AG6=1/2
Then my result will show:
1 Days 1/2 Hours

Do I not have something formatted properly?
How can I get the result to always read properly even if the result equals ZERO?
i.e.
0 DAYS 0 Hours
0 DAYS 3/4 Hours
1 DAYS 0 Hours

Cheers!

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

agihcam

Well-known Member
there might be better way;
Code:
``=IF(AG6=0,SUM(AE6/G111)&" "&"Days"&" "&AG6&" "&"Hrs",IF(SUM(AE6/G111)=0,SUM(AE6/G111)&" "&"Days"&" "&AG6&" "&"Hrs",SUM(AE6/G111+AG6)))``

Special-K

Board Regular
It works ... but wow ... that's a fairly confusing formula to follow.

How would I adapt it to the formula below?
=SUM(AC12/G111-E107)

I tried ... but unsuccessfully.

agihcam

Well-known Member
try;
Code:
``=IF(E107=0,SUM(AC12/G111)&" "&"Days"&" "&E107&" "&"Hrs",IF(SUM(AC12/G111)=0,SUM(AC12/G111)&" "&"Days"&" "&E107&" "&"Hrs",SUM(AC12/G111-E107)))``

Replies
3
Views
111
Replies
3
Views
39
Replies
6
Views
72
Replies
4
Views
147
Replies
1
Views
114