Using Date & Time Custom Cell - Need to show blank!

[JDB]

I have a column with the cells formatted to show dd/mm/yyyy hh:mm. There is
also a formula that adds a day if certain conditions are met. When I copy
this formula down so that every cell has it, the cells on rows that haven't
been used yet show as 01/01/1900 00:00. How do I show these as blank cells -
the sheet is quite confusing otherwise!

[Nick Hodge]

JDB

Use an IF function around your current formula checking for a blank in the
cell that will eventually be filled out (In my example A1)

=IF(A1="","",A1+1)

(These are two double-quotes, "")

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


"JDB" <[email protected]> wrote in message
news:[email protected]...
>I have a column with the cells formatted to show dd/mm/yyyy hh:mm. There is
> also a formula that adds a day if certain conditions are met. When I copy
> this formula down so that every cell has it, the cells on rows that
> haven't
> been used yet show as 01/01/1900 00:00. How do I show these as blank
> cells -
> the sheet is quite confusing otherwise!

[JDB]

This doesn't seem to work. Firstly, it says that this creates a circular
reference.

I should have pointed out that the actual formula that is already in the
cells is as follows:

IF((E3="Y"),"",(B3+1))

Column E is a Y or N answer to whether a particular action has been
completed within 24 hours of the original date. If it hasn't, an email needs
to be sent 24 hours after the original date. The original date is column B.


"Nick Hodge" wrote:

> JDB
>
> Use an IF function around your current formula checking for a blank in the
> cell that will eventually be filled out (In my example A1)
>
> =IF(A1="","",A1+1)
>
> (These are two double-quotes, "")
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> www.nickhodge.co.uk
> [email protected]HIS
>
>
> "JDB" <[email protected]> wrote in message
> news:[email protected]...
> >I have a column with the cells formatted to show dd/mm/yyyy hh:mm. There is
> > also a formula that adds a day if certain conditions are met. When I copy
> > this formula down so that every cell has it, the cells on rows that
> > haven't
> > been used yet show as 01/01/1900 00:00. How do I show these as blank
> > cells -
> > the sheet is quite confusing otherwise!
>
>
>

[David Biddulph]

"JDB" <[email protected]> wrote in message
news:[email protected]...

> "Nick Hodge" wrote:

>> "JDB" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a column with the cells formatted to show dd/mm/yyyy hh:mm. There
>> >is
>> > also a formula that adds a day if certain conditions are met. When I
>> > copy
>> > this formula down so that every cell has it, the cells on rows that
>> > haven't
>> > been used yet show as 01/01/1900 00:00. How do I show these as blank
>> > cells -
>> > the sheet is quite confusing otherwise!

>> Use an IF function around your current formula checking for a blank in
>> the
>> cell that will eventually be filled out (In my example A1)
>>
>> =IF(A1="","",A1+1)
>>
>> (These are two double-quotes, "")

> This doesn't seem to work. Firstly, it says that this creates a circular
> reference.
>
> I should have pointed out that the actual formula that is already in the
> cells is as follows:
>
> IF((E3="Y"),"",(B3+1))
>
> Column E is a Y or N answer to whether a particular action has been
> completed within 24 hours of the original date. If it hasn't, an email
> needs
> to be sent 24 hours after the original date. The original date is column
> B.

So taking what Nick suggested, and including that logic in your formula:
=IF((E3="Y"),"",(IF(B3="","",B3+1)))
--
David Biddulph