Back to Dates in Excel archive index

Back to archive home

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.

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

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

='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

Aladin,

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

Karen

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.

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.