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

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" <to.reply.pls.see.sig@end.of.message.com.invalid> wrote in message
news:d6k4n1pbffg0t9ntk5oh4pdpqhmeisc55j@4ax.com...
> 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><view>deselect " zero values" in window options

--
Greetings from New Zealand
Bill K
"Howie" <to.reply.pls.see.sig@end.of.message.com.invalid> wrote in message
news:d6k4n1pbffg0t9ntk5oh4pdpqhmeisc55j@4ax.com...
> 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!A1>0,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"
<terre08@mvps.org> 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"
<bill.kuunders@xtra.co.nz> wrote:

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

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" <to.reply.pls.see.sig@end.of.message.com.invalid> wrote in message
news:4vban1h0dt08vhfvph83stjbvopb56pbv6@4ax.com...
> On Thu, 10 Nov 2005 09:48:49 +1300, "Bill Kuunders"
> <bill.kuunders@xtra.co.nz> wrote:
>
> |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'?

You could also give the cells a conditional format.

If is equal to 0
font is white.

--
Greetings from New Zealand
Bill K
"Howie" <to.reply.pls.see.sig@end.of.message.com.invalid> wrote in message
news:4vban1h0dt08vhfvph83stjbvopb56pbv6@4ax.com...
> On Thu, 10 Nov 2005 09:48:49 +1300, "Bill Kuunders"
> <bill.kuunders@xtra.co.nz> wrote:
>
> |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'?

On Sun, 13 Nov 2005 20:39:31 +1300, "Bill Kuunders"
<bill.kuunders@xtra.co.nz> 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" <to.reply.pls.see.sig@end.of.message.com.invalid> wrote in message
news:f6jgn1pvks85cp665g62bv6mfcou96alkf@4ax.com...
> On Sun, 13 Nov 2005 20:39:31 +1300, "Bill Kuunders"
> <bill.kuunders@xtra.co.nz> 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"
<bill.kuunders@xtra.co.nz> wrote:

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

And here.

Excellent. thanks again everyone.

H.