#" Days" ?/?" Hours"

Special-K

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

My cell formula reads:
=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!

:confused:
 

Some videos you may like

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
Joined
Jan 16, 2006
Messages
1,624
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
Joined
Apr 18, 2006
Messages
63
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.

Thank you for your help!
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,112,211
Messages
5,541,845
Members
410,550
Latest member
ganeshsamant
Top