Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home

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.

Re: adding text to a formula

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?

Aladin


Re: adding text to a formula

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


Re: adding text to a formula

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

Aladin,

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

Karen


Re: adding text to a formula

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).","")

Aladin

===========


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.