Formula is entering a default time when it comes across an empty cell..

H

Howie

I'm using a formula to copy a time from one cell to another
across sheets. The format of the time is h:mm AM/PM.

However, when the formula references an empty cell, it puts in a
default value of 12:00 AM and I need it to remain blank, (just as
the referenced cell)
It's such a simple copy formula. ie:

=sheet1!A1

I can't see anything causing this.
Has anyone any ideas?

thanks in advance,

Howard.
 
P

Peo Sjoblom

=IF(Sheet1!A1="","",Sheet1!A1)

you refer to another cell a (blank cell equals zero) thus the
12:00 AM which is the same as zero time, if you format the cell as General
you'll see 0
 
R

Ron Coderre

In Excel, if the formula refers to cell A1 and A1 is blank, the result is zero.

Try one of these:
=IF(ISBLANK(Sheet1!A1),"",Sheet1!A1)
or
=IF(ISNUMBER(Sheet1!A1),Sheet1!A1,"")
or
=IF(Sheet1!A1>0,Sheet1!A1,"")

Do any of those help?

••••••••••
Regards,
Ron
 
H

Howie

On Wed, 9 Nov 2005 12:45:23 -0800, "Peo Sjoblom"

|=IF(Sheet1!A1="","",Sheet1!A1)
|
| you refer to another cell a (blank cell equals zero) thus the
|12:00 AM which is the same as zero time, if you format the cell as General
|you'll see 0

Ahh. I see. Thank you for that. It's a shame to have to enter an
IF statement as I have many formulas to enter-in manually. A
couple of clicks per cell would have been much easier!

However, it solves it, so thank you!

H.
 
H

Howie

On Thu, 10 Nov 2005 09:48:49 +1300, "Bill Kuunders"

|one way
|
|<tools><options><view>deselect " zero values" in window options

Ahh, yes. This is much simpler. However, if I send my sheet to
soomebody, will it just re-insert the 0 value if their version of
excel is not set up as deselecting zero values?

If so, I have now changed the cell format to custom:

[$-409]h:mm AM/PM;@

But I still get the zero value entered by the formula. (Only this
time it shows as 12:00 AM of course).
I don't suppose there is a way to alter the above custom cell
format line to ignore zeros'?
 
B

Bill Kuunders

You could enter a macro in the "this workbook" "before open"

Private Sub Workbook_Open()
ActiveWindow.DisplayZeros = False
End Sub

and in the "before close"

ActiveWindow.DisplayZeros = True
 
B

Bill Kuunders

You could also give the cells a conditional format.

If is equal to 0
font is white.
 
H

Howie

On Sun, 13 Nov 2005 20:39:31 +1300, "Bill Kuunders"

|You could also give the cells a conditional format.
|
|If is equal to 0
|font is white.

Thanks for those hints bill.
f I use the conditional format option, can it be an additional
condition to the one I already have? (see my posting, above)

Thanks again,

H.
 
H

Howie

On Tue, 15 Nov 2005 08:18:16 +1300, "Bill Kuunders"

|Yep,
|It worked on this side of the world.

And here.

Excellent. thanks again everyone.

H.
 
Top