Format numbers to read "? Days and ? Hours"

[AndyTampa]

I have a set of numbers after calculating a start date and end date. I create a pivot table of these numbers, but I need them to read in Days and Hours instead of some decimal. The data from the pivot table will be used to make a chart.

This means I cannot use a formula to concatenate the number into text because the chart won't display the data bars correctly, if at all. In formatting cells, I can make 33.34 read as 33.34 Days or 800 Hours, but I can't find a way to make it read "33 Days 8 Hours".

I've used d" Days "h" Hours" which works until you get over 30 days. Excel 2010 will not accept [d]" Days "h" Hours" or #" Days "h" Hours". Am I missing something?

 

[CodeNinja]

=INT(A1) &" Days, " &INT(MOD(A1,1)*24) &" Hours"

If you want the minutes, you would have to mod the remainder of the hours etc

 

[AndyTampa]

That is a formula which won't work in a bar graph nor can 'x' number of items be averaged on that. I need to format a number to read Days and Hours.

 

[AndyTampa]

Is there a way to calculate within a custom cell format?