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.

--
Howard Coakley
e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from www.skype.com)

=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


--

Regards,

Peo Sjoblom

"Howie" wrote in message
...
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.

--
Howard Coakley
e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from www.skype.com)

one way

<tools<options<viewdeselect " zero values" in window options

--
Greetings from New Zealand
Bill K
"Howie" wrote in message
...
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.

--
Howard Coakley
e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from www.skype.com)

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!A10,Sheet1!A1,"")

Do any of those help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"Howie" wrote:

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.

--
Howard Coakley
e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from www.skype.com)

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

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

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

|one way
|
|<tools<options<viewdeselect " 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'?

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


--
Greetings from New Zealand
Bill K
"Howie" wrote in message
...
On Thu, 10 Nov 2005 09:48:49 +1300, "Bill Kuunders"
wrote:

|one way
|
|<tools<options<viewdeselect " 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'?

You could also give the cells a conditional format.

If is equal to 0
font is white.

--
Greetings from New Zealand
Bill K
"Howie" wrote in message
...
On Thu, 10 Nov 2005 09:48:49 +1300, "Bill Kuunders"
wrote:

|one way
|
|<tools<options<viewdeselect " 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'?

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

|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.

--
Howard Coakley
e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from www.skype.com)

Yep,
It worked on this side of the world.


--
Greetings from New Zealand
Bill K
"Howie" wrote in message
...
On Sun, 13 Nov 2005 20:39:31 +1300, "Bill Kuunders"
wrote:

|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.

--
Howard Coakley
e-mail... howard<dot}coakleyatcoakley<dot].codotuk
Skype ID: howie10 (get skype from www.skype.com)

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

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

And here.

Excellent. thanks again everyone.

H.