MrExcel Publishing
Your One Stop for Excel Tips & Solutions

adding text to a formula

Posted by Karen on June 16, 2001 12:56 PM

I know this is probably really easy, but I just can't figure it out. I have a formula [=IF(SUM(M9:M28)=0," ",SUM(M9:M28))] that I want to add text to if the value is false. For example, the formula is calculating time values and if the answer is 0, I want the cell to be blank. If the answer is greater than 0, I want the cell to read the number and the word "hours". Any suggestions? Any help would be immensely appreciated.

Posted by Aladin Akyurek on June 16, 2001 1:16 PM

Assuming that your SUM formula results in

16:40 [ Is this the format that you have? ],

are you seeking to have this result to be:

16 hours and 40 minutes?


Posted by Joe Was on June 16, 2001 11:12 PM

On sheet X where you need the data;
='Sheet y'!D9 & " and " & 'Sheet y'!E9
The trick is to reference the sheet name with a ! next to the Cell address or Range, like Sheet1! or 'Sheet1'! both work. The concatenation is done by space then "&" space with no quotes around the ampersand. You must add any formatting on your own!
If D9=123 and E9=Test
then, =D9 & E9 gives 123Test and
=D9 & "and" & E9 gives 123andTest
=D9 & " and " & E9 gives 123 and Test. JSW

Posted by Karen on June 17, 2001 3:57 PM


Yes that is the time format. I am doing an overtime expenditure sheet. Any help you can give would be greatly appreciated.


Posted by Aladin Akyurek on June 17, 2001 11:50 PM

Karen -- Is this what you want?

=IF(COUNT(M9:M28)>0,HOUR(SUM(M9:M28))&" hours and "&MINUTE(SUM(M9:M28))&" minute(s).","")