Date Not Null
 

Column A Row 6 is an unprotected date field (mm/dd/yyyy).
Column H Row 6 is a protected date field (mm/dd/yyyy).

When you enter a date (i.e., 01/01/2006) into A6, 180 days are added to the
date and is displayed in H6 (=A6 + 180) with the result being 06/30/2006.

When A6 is null, H6 displays 06/28/1900.

First of all, am I using the right method to add 180 days to A6.
If yes, how can I make H6 not display the odd date when A6 is null.

Thank you in advance for your assistance.

Rick

Date Not Null
 

Hi Rick,

If you'd like to add 180 days to a particular date, one way to go would be
to set cell H6 as follows:

H6 =DATE(YEAR(A6),MONTH(A6),DAY(A6)+180)

If you'd like nothing to be displayed when cell A6 contains nothing, modify
the above formula to:

H6 =if(ISBLANK(A6),"",DATE(YEAR(A6),MONTH(A6),DAY(A6) +180))

And, finally, if you'd like to ensure that, if a date isn't entered in A6,
you get an error message:

H6
=IF(ISBLANK(A6),"",IF(ISERR(DATE(YEAR(A6),MONTH(A6 ),DAY(A6)+11)),"Invalid",DATE(YEAR(A6),MONTH(A6),D AY(A6)+11)))

Hope this helps.

Paul

"Rick_C" wrote:

Column A Row 6 is an unprotected date field (mm/dd/yyyy).
Column H Row 6 is a protected date field (mm/dd/yyyy).

When you enter a date (i.e., 01/01/2006) into A6, 180 days are added to the
date and is displayed in H6 (=A6 + 180) with the result being 06/30/2006.

When A6 is null, H6 displays 06/28/1900.

First of all, am I using the right method to add 180 days to A6.
If yes, how can I make H6 not display the odd date when A6 is null.

Thank you in advance for your assistance.

Rick

Date Not Null
 

=IF(A6="","",A6+180)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Rick_C" wrote in message
...
Column A Row 6 is an unprotected date field (mm/dd/yyyy).
Column H Row 6 is a protected date field (mm/dd/yyyy).

When you enter a date (i.e., 01/01/2006) into A6, 180 days are added to
the
date and is displayed in H6 (=A6 + 180) with the result being 06/30/2006.

When A6 is null, H6 displays 06/28/1900.

First of all, am I using the right method to add 180 days to A6.
If yes, how can I make H6 not display the odd date when A6 is null.

Thank you in advance for your assistance.

Rick

Date Not Null
 

Incidentally, Rick, your approach of adding 180 directly to cell A6 is
correct also (and briefer than my previous suggestion but you can use the
other logic to help you with null and non-date values in A6:)

"Rick_C" wrote:

Column A Row 6 is an unprotected date field (mm/dd/yyyy).
Column H Row 6 is a protected date field (mm/dd/yyyy).

When you enter a date (i.e., 01/01/2006) into A6, 180 days are added to the
date and is displayed in H6 (=A6 + 180) with the result being 06/30/2006.

When A6 is null, H6 displays 06/28/1900.

First of all, am I using the right method to add 180 days to A6.
If yes, how can I make H6 not display the odd date when A6 is null.

Thank you in advance for your assistance.

Rick

Date Not Null
 

Thank You Bob and Paul...

It worked great but now I have another request. I did not originally post it
since I thought that the same formula would work for the next problem... but
it does not.

Here is the scenario:

D8 is a number field. When D8 is populated with a number (i.e., 10), that
number is subtracted from H6 (see below for the format) , which is generated
by the date entered into A6. Field H8 (protected date formated as mm/dd/yyyy)
gets the end result.

Example: A6 = 01/01/2006 so H6 = 06/30/2006.
If D8 is 10 then H8 shoudl be 06/20/2006.

Now, if A6 is null, then H6 is blank thanks to your help. However, H8 shows
#Value!

How can I make H8 blank if there is no data.

Thank you for all your help. Usually I do all this in Access and I know how
to do it there but Excel is a new breed for me :)

Much appreciated...

Rick




"Bob Phillips" wrote:

=IF(A6="","",A6+180)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Rick_C" wrote in message
...
Column A Row 6 is an unprotected date field (mm/dd/yyyy).
Column H Row 6 is a protected date field (mm/dd/yyyy).

When you enter a date (i.e., 01/01/2006) into A6, 180 days are added to
the
date and is displayed in H6 (=A6 + 180) with the result being 06/30/2006.

When A6 is null, H6 displays 06/28/1900.

First of all, am I using the right method to add 180 days to A6.
If yes, how can I make H6 not display the odd date when A6 is null.

Thank you in advance for your assistance.

Rick

Date Not Null
 

Disregard Paul and Bob...

I figured out the formula...

Thanks much for your help.

Rick



"Rick_C" wrote:

Thank You Bob and Paul...

It worked great but now I have another request. I did not originally post it
since I thought that the same formula would work for the next problem... but
it does not.

Here is the scenario:

D8 is a number field. When D8 is populated with a number (i.e., 10), that
number is subtracted from H6 (see below for the format) , which is generated
by the date entered into A6. Field H8 (protected date formated as mm/dd/yyyy)
gets the end result.

Example: A6 = 01/01/2006 so H6 = 06/30/2006.
If D8 is 10 then H8 shoudl be 06/20/2006.

Now, if A6 is null, then H6 is blank thanks to your help. However, H8 shows
#Value!

How can I make H8 blank if there is no data.

Thank you for all your help. Usually I do all this in Access and I know how
to do it there but Excel is a new breed for me :)

Much appreciated...

Rick




"Bob Phillips" wrote:

=IF(A6="","",A6+180)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Rick_C" wrote in message
...
Column A Row 6 is an unprotected date field (mm/dd/yyyy).
Column H Row 6 is a protected date field (mm/dd/yyyy).

When you enter a date (i.e., 01/01/2006) into A6, 180 days are added to
the
date and is displayed in H6 (=A6 + 180) with the result being 06/30/2006.

When A6 is null, H6 displays 06/28/1900.

First of all, am I using the right method to add 180 days to A6.
If yes, how can I make H6 not display the odd date when A6 is null.

Thank you in advance for your assistance.

Rick